CODEWITHSUNDEEP
sql
Christian Mayne
Christian Mayne

Reputation: 1749

Counting occurrences of distinct multiple columns in SQL

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?

Upvotes: 21

Views: 28730

Answers (3)

Eugen Rieck
Eugen Rieck

Reputation: 65274

SELECT cityandcountry, count(*) as occurrences FROM (
  SELECT DISTINCT concat(city, country) FROM tablename
) as baseview;

if you want city and country preformated, or

SELECT cityandcountry, count(*) as occurrences FROM (
  SELECT DISTINCT city, country FROM tablename
) as baseview;

if not.

Upvotes: 1

aF.
aF.

Reputation: 66697

What you need is a group by:

Select city, country, count(*) as counter
from usertable
group by city, country

Upvotes: 10

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

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

Upvotes: 31

Related Questions