Список городов с количеством их жителей

Условие

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

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

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

-- Таблица "users"
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);

``

Ответ

INNER JOIN исключит города без пользователей. Группировка по идентификатору и названию даст Москве 2 пользователя, Санкт-Петербургу — 1.

Запрос:

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

Краснодар в результат не попадёт, поскольку соответствующих строк users нет. Группировка только по name могла бы объединить разные города с одинаковым названием, поэтому используется и id. Первичные ключи и внешний ключ из условия обеспечивают однозначное присоединение пользователя к городу; DISTINCT для такого соединения не требуется.