CODEWITHSUNDEEP
sqldatabasecount
M. Eichhorn
M. Eichhorn

Reputation: 39

How to get 0 as a Count-output?

SELECT Country.NAME, COUNT(City.NAME) as Number_of_cities
FROM City
JOIN Country ON City.COUNTRY = Country.CODE
WHERE City.LONGITUDE IS NULL AND City.LATITUDE IS NULL
GROUP BY Country.NAME, City.COUNTRY;

As the code is now, countries which only got cities with a longtidude and latitade value arent shown. How do i have to change the code, so that countries where all cities have longtitude and latidude values show 0 as coun?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You want a left join, with the filtering in the on clauses:

SELECT co.NAME, COUNT(ci.country) as Number_of_cities
FROM country co left join
     City ci
     ON ci.COUNTRY = co.CODE and
        ci.LONGITUDE IS NULL and
        ci.LATITUDE IS NULL
GROUP BY co.NAME;

Upvotes: 1

Related Questions