Ambiguous Columns and Count

Question

My query requires me to find the busiest location by number of people (Schema Attached)

select DISTINCT location.name as 'Location', f_name|| ' ' || l_name as 'Citizen'
from CheckIn
join location on checkin.LocID = Location.LocID
join  person on person.PersonID = CheckIn.PersonID

With the above query. I can find the people who visited the locations but I cannot find the most number of people who visited a location as they all show individually. I know I need to add a count or group by.

If I try to put the count as per below

select DISTINCT location.name as 'Location', f_name|| ' ' || l_name as 'Citizen', count (personid)
from CheckIn
join location on checkin.LocID = Location.LocID
join  person on person.PersonID = CheckIn.PersonID

It will show me ambiguous column. I know it is saying that because I have used it as a join but then how do I count the persons If I cannot reuse it?

How do I fix this code to show me the busiest location by number of people?

Answer 1

This query:

SELECT LID, COUNT(*) 
FROM CheckIn
GROUP BY LID

returns the number of people visited each LID.
You can also use window function RANK() to rank each location by the number of people:

SELECT LID, RANK() OVER (ORDER BY COUNT(*) DESC) rnk 
FROM CheckIn
GROUP BY LID

Finally you use this query with the operator IN to return the details of the locations ranked first:

SELECT * FROM Location 
WHERE LocationID IN (
  SELECT LID
  FROM (
    SELECT LID, RANK() OVER (ORDER BY COUNT(*) DESC) rnk 
    FROM CheckIn
    GROUP BY LID
  )
  WHERE rnk = 1
)