MySQL count distinct values in multiple columns

Question

I need to count the distinct number of people who visit each restaurant location. Multiple visits by the same person to the same location should only be counted once.

Input:

location    visitor
-------------------
sydney      john
melbourne   john
melbourne   jane
melbourne   pete
sydney      paul
melbourne   jane
sydney      jake
sydney      john
sydney      john
melbourne   jake
sydney      john

Expected output:

location    count
-----------------
sydney      3
melbourne   4

I've tried to follow examples here Using group by on multiple columns, nested SQL queries like this one

select distinct location,count(*) as c from (select distinct location,distinct visitor from guests) group by location,visitor order by c desc

and endless Googling with no luck.

Answer 1

You need to group by the location, but your DISTINCT is per visitor

select 
      location,
      count( DISTINCT visitor) as c
   from 
      guests
   group by 
      location
   order by 
      c desc

However, considering your SAMPLE data is not good. It would be better having lookup tables like Locations which has an ID and Location, and a People table with ID and Name. Then your Visits table would have a Location_ID and a Person_ID.

What if you had a John Doe John Smith.

By just having John is not truly distinct and would give a false answer.