Список городов с числом их пользователей, включая нулевые значения

Условие

Необходимо получить перечень всех городов и количество пользователей, привязанных к каждому из них. Даже если в каком‑то городе нет ни одного пользователя, он всё равно должен присутствовать в результате с числом 0.

-- Таблица "towns"
create table towns (
    id    serial primary key,
    name  text not null
);

insert into towns (name)
values
    ('Москва'),
    ('Санкт-Петербург'),
    ('Краснодар');

-- Таблица "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
    ('Иван', 1),
    ('Анна', 1),
    ('Олег', 2);

-- Запрос
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;

Ответ

Используйте LEFT JOIN от городов и COUNT ненулевого идентификатора клиента. Тогда город без совпадений получит 0, а не ошибочную единицу от COUNT(*).

Запрос:

SELECT t.name, COUNT(c.id) AS user_count
FROM towns AS t
LEFT JOIN clients AS c ON c.town_id = t.id
GROUP BY t.id, t.name
ORDER BY t.id;

Результат: Москва 2, Санкт-Петербург 1, Краснодар 0. Вариант RIGHT JOIN из условия тоже сохраняет города, но GROUP BY только имени объединяет разные города с одинаковым названием. COALESCE вокруг COUNT здесь не требуется: COUNT сам возвращает 0.