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;