Hello everyone,
I have 2 tables. One with a list of countries and another with a list of users. The users table stores the Country they are from as well as other info.
Eg the structure is a little bit like this...
Countries:
--
CountryId
CountryName
Users:
UserId
UserName
CountryId
So, the question is how to a list all my countries with total users in each. Eg, so the results are something like this......
CountryName TotalUsers
United Kingdom 334
United States 1212
France 433
Spain 0
Any help woulld be great as I have been fumbling with this all morning. Im not 100% with SQL yet!!
Cheer
Stephen
here You go...
Code Snippet
Create Table #countries (
[CountryId] int ,
[CountryName] Varchar(100)
);
Insert Into #countries Values('1','USA');
Insert Into #countries Values('2','UK');
Insert Into #countries Values('3','IN');
Create Table #users (
[UserId] int ,
[UserName] Varchar(100) ,
[CountryId] int
);
Insert Into #users Values('1','John','1');
Insert Into #users Values('2','Dale','1');
Insert Into #users Values('3','Thome','2');
--With ZERO COUNT
Select
[CountryName],
Count([UserId])
from
#countries C
left Outer Join #users U on C.[CountryId] = U.[CountryId]
Group By
[CountryName]
--Without ZERO COUNT
Select
[CountryName],
Count([UserId])
from
#countries C
Inner Join #users U on C.[CountryId] = U.[CountryId]
Group By
[CountryName]
|||Super! Many thanks and thank you for replying so quickly.
Makes sense now - easier than what I was trying to do!!! 
