Count number of users from a certain country

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

This will give what you want. But you might want to cache the result of the query. With a lot of users it’s quite a heavy query.

SELECT
    country,
    COUNT(*)
FROM
    users
GROUP BY
    country

Perhaps a better idea is (assuming you don’t need the counts) to do it like this:

SELECT
    DISTINCT country
FROM
    users