I made a simple database containing two tables, ‘country’ and ‘human’:
+------+---------+ | id | name | +------+---------+ | 1 | Finland | | 2 | Sweden | | 3 | Norway | | 4 | Denmark | +------+---------+ +--------+----------+ | name | homeland | +--------+----------+ | Liisa | 1 | | Matti | 1 | | Holger | 2 | +--------+----------+
This query shows that nobody lives in Norway, but why it says nothing about Denmark?
select country.name, count(human.name) from human right join country on human.homeland = country.id group by homeland; +---------+-------------------+ | name | count(human.name) | +---------+-------------------+ | Norway | 0 | | Finland | 2 | | Sweden | 1 | +---------+-------------------+
I am using MariaDB, but prefer solutions that works on other DBs too.
Advertisement
Answer
You typically GROUP BY
the same columns as you SELECT
, except those who are arguments to set functions:
select country.name, count(human.name) from country left join human on human.homeland = country.id group by country.name;