Problem w/ Parameter Value? 1

[NFLDUser]

I have a table called PatientList with a field called 'city'. I want to find the TOP 10 cities for all rows. Here's my code:

SELECT TOP 10 city, newvar=Count(*)
FROM PatientList
GROUP BY city
ORDER BY newvar ASC

But I keep getting asked for a value for newvar. But newvar is supposed to be a new variable. What am I doing wrong?

 

[PHV]

SELECT TOP 10 city, Count(*) AS newvar
FROM PatientList
GROUP BY city
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NFLDUser]

Thanks PHV, that works perfect.

How could I go one step beyond that and also display a field that shows what percentage each city represents overall?

 

[PHV]

One way:
SELECT TOP 10 city, Count(*) AS newvar
, (Count(*)/(SELECT Count(*) FROM PatientList)) AS [%]
FROM PatientList
GROUP BY city
ORDER BY 2 DESC

Another way:
SELECT TOP 10 P.city, Count(*) AS newvar
, (Count(*)/T.overall) AS [%]
FROM PatientList AS P, (
SELECT Count(*) AS overall FROM PatientList
) AS T
GROUP BY P.city, T.overall
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NFLDUser]

Excellant!
Thanks again.