oneearbear oneearbear - 12 days ago 6
SQL Question

ORACLE SQL - display count and 0

I have a simple code displaying the countrynames and the amount of citys with a population between 100000 and 200000 in it. Now i am trying to display the countrynames where are no citys with this populationrange as well. The count for this citys should be displayed as 0 in the table.

SELECT country.name, COUNT(City.population)
FROM (city JOIN country ON city.country = country.code)
WHERE city.population BETWEEN 100000 AND 200000
GROUP BY country.name
ORDER BY country.name;


thank you

Answer

Do a LEFT JOIN to also include countries without any cities with that population:

SELECT country.name, COUNT(City.population) 
FROM country
LEFT JOIN city
    ON  city.country = country.code
    AND city.population BETWEEN 100000 AND 200000
GROUP BY country.name
ORDER BY country.name;
Comments