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;