Sobes.tech
Junior — Senior

List of cities with the number of their users, including zero values

livecode

Task condition

It is necessary to obtain a list of all cities and the number of users associated with each of them. Even if there are no users in a city, it should still be present in the result with a count of 0.

-- Table "towns"
create table towns (
    id    serial primary key,
    name  text not null
);

insert into towns (name)
values
    ('Moscow'),
    ('Saint Petersburg'),
    ('Krasnodar');

-- Table "clients"
create table clients (
    id        serial primary key,
    name      text not null,
    town_id   int not null references towns(id)
);

insert into clients (name, town_id)
values
    ('Ivan', 1),
    ('Anna', 1),
    ('Oleg', 2);

-- Query
SELECT towns.name,
       COALESCE(COUNT(clients.name), 0) AS user_count
FROM clients
RIGHT JOIN towns
ON towns.id = clients.town_id
GROUP BY towns.name;