sql server 2000 group by 1

[pssheba]

Hi !
I'm searching a select statement to show my table in groups.
Id like to show al members living in city: "a" and at the end of the list a number showing how many living in "A".
Than city "B" and at the end the total of members.
i wrote:

select
lastname,
city,
count(city)
from
mytable
group by city

and recieved an error message saying:

olumn 'LASTNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Could anyone please tell me how i can show all members' details grouped by CITY field ?

 

[bborissov]

SELECT mytable.LastName, 
       mytable.City,
       Tbl1.Cnt AS CityCount
FROM mytable
INNER JOIN (SELECT City, COUNT(*) AS Cnt
                   FROM mytable
                   GROUP BY City) Tbl1
ON mytable.City = Tbl1.City

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Thank you bborissov !
Is there a way to see each client separately at the list and only at the end of each city's list to see the total number of clients ?
Is there a way to add the city's name as a title as a header of each city ?
Thanks again !

 

[bborissov]

SELECT mytable.LastName,
       mytable.City,
       CAST(0 as int) AS Cnt,
       0              AS OrdBy
FROM mytable
UNION ALL
SELECT ''   AS LastName
       City,
       COUNT(*) AS Cnt,
       1        AS OrdBy
FROM mytable
GROUP BY City
ORDER BY City, OrdBy

(not tested), But I think this is a job for your frontend.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Thanks a lot bborissov.
It'll take a while till i understand exactly what you did but it works and thats what matters !

 

[ESquared]

Using WITH ROLLUP you could possibly get the results you want with a single query.