SQL COUNT and Inner Join Issue

Hi

I have two tables

1. Members - which contains all records of members including city_id
2. city_list - which contains city name and city_id

Now i need to generate report as below

List of city with numbers of members

This will will let me know how popular is my site in citywise .


--------------------
I had tried this sql query but no results

select city_id,count(*) ,city_list.city_name from members inner join city_list on city_list.city_id=members.city_id group by city_id order by count(*) desc


-----------------
My database is in MS SQL Server 2003


Thanks in advance

Suraj jain

 

Ohh .. i myself got the solution ... here is the exact query ..

==========================
select city_name, (select count(members.city_id) from members where city_list.city_id=members.city_id) as tagcount from city_list order by tagcount desc
==========================

Do lemme .. if there is still a better way to do it



Thanks for reading ..
bye

 

Shouldn't this work (you must include all fields in the group by).

SELECT C.city_id, C.city_name, Count(1) FROM city_list C
JOIN members M ON M.city_id = C.city_id
GROUP BY C.city_id, C.city_name ORDER BY Count(1) DESC

 

what is the meaning of tagcount here could you pls tell.I always mess in this type of generall problem.

 

Thx for this infor too it works well, as im not sure about the tag count

 

tagcount is alias name.