Count number of users from a certain country

Question

Hi everybody,

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

Answer 2

Sounds like you want something like this...?

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

Answer 3

This is pretty straightforward:

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

You just group your data by country and count the entries for each country.

Or if you want them sorted by the number of visitors, use a different ORDER BY clause:

SELECT
    Country, COUNT(*) AS 'Number'
FROM
     YourTable
GROUP BY
    Country
ORDER BY
    COUNT(*) DESC

Answer 4

If you want the count per country:

select country, count(*) from users group by country;

If you just want the possible values:

select distinct country from users;

Answer 5

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