SQL Query, number of city and continent-CodePudding

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?

CodePudding user response：

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.