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.
CodePudding user response:
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.