Skip to content
Advertisement

Sql join, count references including zeros

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement