database sql join question

Question

i have 2 tables called

• Location (id, name)
• Person (id, name, location_id)

A person has a location Id which joins these tables . . i would like a SQL query that gives me each location and the count of person table for that id.

i could do something like this and then add up the records in code but i want to find out a way that i only get one row per region with count of people in that region

SELECT l.*, r.id from Location l
inner join Person r
on r.location_id = l.id 
order by l.name asc

Answer 1

You want to use aggregates and the GROUP BY clause

   SELECT l.id, l.name, count(r.id)    
   FROM  Location l    
   INNER JOIN Person r on r.location_id = l.id    
   GROUP BY l.id., l.name    
   ORDER BY l.name asc

Answer 2

  SELECT lo.name, COUNT(*)
    FROM LOCATION lo
    JOIN PERSON p ON p.location_id = lo.id
GROUP BY lo.name
ORDER BY lo.name

Answer 3

try this

select count(*), Location.name, Location.id from Location, Person where Person.location_id = Location.id group by Location.id

Answer 4

Try:

  Select L.Name, Count(*) PersonsCount
  From Location L 
    Join Person P On P.Location_Id = L.Id
  Group By L.Name

or if you want to see Locations with zero counts,

  Select L.Name, Count(*) PersonsCount
  From Location L 
    Left Join Person P On P.Location_Id = L.Id
  Group By L.Name