SummerDays SummerDays - 2 months ago 7
SQL Question

Group By SQL Statement - Get countries with more than 5 cities

I am trying to pull the countries that have more than 5 cities.

Tables:

City

city_id, city, country_id, last_update


Country
country_id, country, last_update


I think I am very close to getting this figured out, but I'm not quite there. Any pointers?

SELECT DISTINCT country
FROM country C, city O
WHERE O.country_id = C.country_id AND O.country_id
IN (SELECT country_id FROM city group by country_id having count(country_id) > 5);

Answer
select country
from country inner join city on city.country_id = country.country_id
group by country
having count(distinct city) > 5