I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table).
Is it possible to make a single query that returns both the country name and the count of people who has that country id in their country column?
like
country users
-------- -----
uk 4
portugal 2
usa 10
...
You have to aggregate using GROUP BY SELECT statement, which is part of the SQL standard (and quite popular to be honest).
Although You haven't stated which database engine You are using, the query below should work in most, if not all products, because it uses very basic syntax:
SELECT b.country, count(a.id) AS user_count
FROM countries b
LEFT JOIN users a ON b.id = a.country_id
GROUP BY b.country;
The query could use an inner join instead of the left outer join. In that case it would exclude countries for which there are no matches in the users table.
If You are thinking about writing more queries, it might be better to try some online SQL course. Code Academy one is rather good. Other noteworthy example is from SQLCourse, but other ones found on the Internet might be OK too. Checking the SELECT statement's online documentation on Your database's webpage might be a good idea as well (as implementations do vary in some details).
