I made a simple database containing two tables, ‘country’ and ‘human’:
x
+------+---------+
| 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;