SQL question about counting...

Question

I want to make a query so that I can grab only Locations that have at least 50 Places.

I have a table of Locations:

Id, City, Country
1, Austin, USA
2, Paris, France

And a table of Places connected to Locations by Location_id

Id, Name, Details, Location_id
1, The Zoo, blah, 2
2, Big Park, blah, 2

I can join them like so:

SELECT places.name, places.id, locations.country, locations.city FROM places INNER JOIN locations ON places.location_id = locations.id

by how can I only get the results of cities that have at least 50 places and order them by the largest amount?

Thanks!

Answer 1

Use a GROUP BY with a HAVING clause.

SELECT locations.country, locations.city, COUNT(*)
FROM places
     INNER JOIN locations ON places.location_id = locations.id
GROUP BY locations.country, locations.city
HAVING COUNT(*) >= 50

Answer 2

Somewhat unperformant, but should work:

SELECT places.name, places.id, sq.country, sq.city, sq.counter FROM (SELECT Locations.id, country,city, count(*) as counter FROM Locations JOIN Places ON (Locations.Id=Places.Location_id) GROUP BY locations.id HAVING count(*) >= 50) AS sq JOIN Places ON (sq.id=Places.Location_id) ORDER BY counter DESC`

P.S. The exact syntax may vary depending on the database, I'm not sure if this is mysql-compatible as is.

Answer 3

You can use the having clause to limit these rows by the value of an aggregate column. Also, MySQL allows you to use lazy group bys, so you can absolutely take advantage of this:

select
    l.country,
    l.city,
    p.name,
    p.details,
    count(*) as number_of_places
from
    locations l
    inner join places p on
        l.id = p.location_id
group by
    l.id
having
    count(*) >= 50
order by
    number_of_places,
    l.country,
    l.city,
    p.name

Answer 4

OK I've seen that the above answers are almost there but have some mistakes, so just posting the correct version:

SELECT locations.country, locations.city, COUNT(*) as count_of_places
FROM places
     INNER JOIN locations ON places.location_id = locations.id
GROUP BY locations.country, locations.city
HAVING COUNT(*) >= 50
ORDER BY count_of_places;