Counting occurrences of distinct multiple columns in SQL

Question

I'm trying to count the occurrences of a distinct set of cities and countries in a user table.

The table is set out similar to:

userid  city       country
------  ---------  --------------
1       Cambridge  United Kingdom
2       London     United Kingdom
3       Cambridge  United Kingdom
4       New York   United States

What I need is a list of every city, country pair with the number of occurrences:

Cambridge, United Kingdom, 2
London, United Kingdom, 1
New York, United States, 1

Currently I run an SQL query to get the distinct pairs:

$array = SELECT DISTINCT city, country FROM usertable

then read it into an array in PHP, and loop through the array, running a query to count each occurrences for each row in the array:

SELECT count(*) FROM usertable
WHERE city = $array['city']
AND   country = $array['country']

I'm assuming my scant grasp of SQL is missing something - what would be the correct way to do this, preferably without the intervention of PHP?

Answer 1

select city, country, count(*)
from usertable
group by city, country