I have a problem with a query.
enter image description here
I have to find for all the continent: the name of the continent, number of cities and number of country. This is what I did
SELECT co.continent, COUNT(*)
FROM Country co
JOIN City c ON c.countrycode = co.code
GROUP BY co.continent
UNION
SELECT COUNT(*)
FROM Country co2
WHERE co.continent = co2.continent ( <---- ??? )
GROUP BY co2.continent
But I dont know if is it legal the part "WHERE co.continent = co2.continent" because the second quary isn’t a subquary of the first, is it? Is there another way to do this quary?
>Solution :
UNION is not required, a single query with GROUP BY and COUNT aggregate will get the desired result, there could be multiple cities in the same country, a country could appear multiple times, use COUNT(DISTINCT...) to remove duplicates.
SELECT co.continent, COUNT(*) cities, COUNT(DISTINCT co.code) countries
FROM Country co
JOIN City c ON c.countrycode = co.code
GROUP BY co.continent
co.continent = co2.continent in the original union query is invalid. Queries in union are independent from each other.