Количество пользователей в каждом городе

Условие

Составьте SQL‑запрос, который возвращает название города и число пользователей, зарегистрированных в этом городе.

create table cities (
    id serial primary key,
    name text not null
);

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

create table users (
    id serial primary key,
    name text not null,
    city_id int not null references cities(id)
);

insert into users (name, city_id)
values
('Иван', 1),
('Анна', 1),
('Олеся', 2);

-- здесь должен быть SELECT-запрос

Ответ

LEFT JOIN сохранит города без жителей, а COUNT(u.id) вернёт для них 0. Для примера: Москва — 2, Санкт-Петербург — 1, Краснодар — 0.

Запрос:

SELECT c.name, COUNT(u.id) AS users_count
FROM cities c
LEFT JOIN users u ON u.city_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

COUNT(*) был бы неверным для пустого города: LEFT JOIN создаёт одну строку с NULL справа. COUNT(u.id) учитывает только настоящих пользователей. Группировка по id не объединяет разные города с совпадающими названиями. Если пустые города нужно исключить, используйте INNER JOIN.