Count number of users from a certain country

Question

I have a table of users, and in this table I have a country field telling where these people are from (i.e. "Sweden", "Italy", ...). How can I do a SQL query to get something like:

Country     Number
Sweden      10
Italy       50
...         ...

Users select their countries from a list I give to them, but the list is really huge so it would be great to have a SQL query that can avoid using that list, that is look in the DB and give back only those countries which are in the database, because for example I have nobody from Barbados, even if I have that option in the country select field of the signup form :)

Thanks in advance!

Answer 1

If the name of the country is in the Users table, try something like this:

SELECT Country, COUNT (*) AS Number
FROM Users
GROUP BY Country
ORDER BY Country

If the name of the country is in the country table, then you will have to join

SELECT Contries.CountryName, Count (*) AS Number
FROM Users
INNER JOIN Countries
    ON Users.CountryId = Countries.CountryId
GROUP BY Countries.CountryName
ORDER BY Countries.CountryName