Can have extra row

[nikol]

I'm working in Crystal reports XI & using SQL also.
I have a table with columns
list_id name .
when list_id =2, name column has all city names for particular state(like CA)

LIST_ID NAME count
2 Sant Clara 3
2 San jose 6
2 SFO 10

I want to count all the client in a particular city like how many clients in sfo.
Is it possible I can count all the clients in all cities like "Total number of clients"=19

OR Can I make my own list of cities for a particular state( May be in a view) & find the count of all clients in all cities.

 

[jbenson001]

Try:
[/code]
Select Total Number of Clients = Count(*)
From Table
Where List_ID = 2 AND Name = 'SFO'
[/code]

 

[nikol]

Actually, i want to ask Is it possible to add a new row which is "All Cities" before city name. I dont have any privillege to update the tables or columns. So, Can I cretae a view which will gove output as
Name
----
All Cities
San jose
Santa clara
Sfo
---------so on. & when I will pick SFO it will give total no of clients(say 4)& when I pick "all Cities" It should give total number of clients in all cities.

 

[jbenson001]

Do you want to display the list of cities in a front end application, like a dropdownlist in .NET?

 

[nikol]

yes, I'm using crystal report XI which has a parameter field(City). When I refresh my report It ask me which city from lov(List of values). LOV has all city names but not "All Cities" coz this row is not in DB. So Can I make a view which will add "all cities" row in that lov & then when I refresh the report the lov should look like this:
All Cities
San jose
Santa Clara
SFO.

 

[jbenson001]

I am not too familiar with CR, but I think you can add that option to your parameter field.

 

[nikol]

ya,I know in CR I can add as formula like
if {?my Parameter} = 'All Cities'
then
1=1
else
{command.city} = {?my Parameter}.
Its working but not for subreports thats why I want to go for sql method. I'm trying to write sql & view also which will extract city names from db & add new row "all cities" ..I'm not sure if it will work or not. just trying ..

 

[jbenson001]

Ok, let me know how that goes

 

[nikol]

I have a table which has list of cities like
List_id Name
2 San jose
2 SFo
2 santa clara
2 Campbell
I have to find out number of clients in particulat city & also from all cities like
Name Count
San Jose 6
SFO 4
Santa Clara 3
So total number of clients=13
I want to add a row "All Cities" Which will have all the list of city. I want the output that when I select san jose It should give me 6 count & when I select "all cities" it should give me the count=13. Is it possible to write simple SQL or by creating view.

 

[nikol]

Do U find any way ?

 

[cmmrfrds]

Possibly a Union.

Select
list_id
city,
count(*) as cnt
From yourtable
where list_id = 2
Group by list_id, city
Union All
Select
'2' as list_id,
'AllCities' as city,
count(*) as cnt
From yourtable
where list_id = 2

 

[nikol]

Hi,
Its almost working. There r 20 cities & cnt is giving 20 count for "AllCities" but when I select "All Cities" in CR its giving me notthing. But the query is working for all other seperate cities.
Thanks for ur help.

 

[cmmrfrds]

count for "AllCities" but when I select "All Cities" in CR its giving me notthing.

The select has a space between all and cities did you account for that? They are not the same as is.

 

[nikol]

Sorry about the space. Ur query works fine for the first step( Row "allcities" has all cities in it)& also for total number of clients in particular city(Which is working fine too). Now I'm want to calculate total number of clients in "allcities" (For now which is coming null)
Name No. Of Clients
Like AllCities 17(Trying to get this number.)
San Jose 7
Santa clara 6
SFO 4
No. Of Clients is in another table.

 

[cmmrfrds]

Can you show the SQL so that we can see how Clients is connected?

 

[nikol]

select distinct count(clients.uid),allcity.name
FROM (((clients INNER JOIN
da_answer ON clients.uid=da_answer.client_id) INNER JOIN
secunsec ON da_answer.uid=secunsec.uid) FULL OUTER JOIN
picklist_values ON secunsec.val_int=picklist_values.uid) FULL OUTER JOIN
allcity ON picklist_values.list_id=allcity.list_id
group by allcity.name

Allcity is a view

(select uid, list_id,
case
when (uid=11 or uid=12 or uid=13 or
uid=14 or uid=15 or uid=16 or uid=17 or uid=18 or uid=19
or uid=20 or uid=21 or uid=23 or uid=24 or uid=26) then 'All Cities'
end name
from picklist_values
where list_id=2 )