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?

score 0 · Accepted Answer · answered Aug 17 '20 at 09:18

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
)

score 0 · Answer 2 · answered Aug 18 '20 at 06:58

0

You can try below:

select 
  name, COUNT(P.PersonID) as People_Count
from Location AS L
inner join checkin as C on C.LocID = L.LocID
inner join  person as P on P.PersonID = C.PersonID
group by name
order by COUNT(P.PersonID) desc

answered Aug 18 '20 at 06:58

Jignesh Bhayani

83
3

Ambiguous Columns and Count

2 Answers2