GROUP BY и HAVING в SQL: группировка строк с примерами

GROUP BY собирает строки в группы по значению колонки и возвращает по одной строке на группу. Внутри группы считаются агрегаты: count, sum, avg, min, max. WHERE отсекает строки до группировки, HAVING фильтрует уже посчитанные группы. Дальше всё на одном наборе заказов, результаты в таблицах взяты из PostgreSQL 16.

users

orders

Что делает GROUP BY

SELECT status, count(*) AS orders_count
FROM orders
GROUP BY status;

Шесть заказов превратились в две строки. Каждое различное значение status дало группу, count(*) посчитал, сколько строк в неё попало. Исходных id в выдаче больше нет, и достать их оттуда уже нельзя. Если строки нужно сохранить и дописать к каждой итог по её группе, это работа для оконных функций, а не для GROUP BY.

Порядок групп произвольный. PostgreSQL вернул cancelled первым, хотя в таблице первым идёт paid: группировка не сортирует, и без явного ORDER BY на порядок полагаться нельзя. Поэтому дальше у каждого запроса с несколькими группами стоит ORDER BY: без него таблицы ниже не воспроизвести.

Агрегаты: count, sum, avg, min, max

Каждый считается по строкам своей группы и отдаёт одно значение.

SELECT status,
       count(*) AS orders_count,
       sum(amount) AS total,
       round(avg(amount), 2) AS avg_amount,
       min(amount) AS min_amount,
       max(amount) AS max_amount
FROM orders
GROUP BY status
ORDER BY status;

round здесь не косметика. Без него тот же avg возвращает 1160.0000000000000000: деление numeric тянет длинный хвост, и в выгрузке он читается как сбой формата. Средний чек почти всегда оборачивают в round(..., 2).

Чем count(*) отличается от count(колонка) и count(DISTINCT)

Разница видна там, где есть NULL или повторы. Заведём вторую таблицу: канал, из которого пришёл заказ. У двух заказов метка потерялась.

order_channel

SELECT o.status,
       count(*)                  AS rows_total,
       count(c.channel)          AS with_channel,
       count(DISTINCT c.channel) AS channels,
       count(DISTINCT o.user_id) AS buyers
FROM orders o
JOIN order_channel c ON c.order_id = o.id
GROUP BY o.status
ORDER BY o.status;

Одна группа из пяти строк, четыре счётчика с разным смыслом. count(*) в значения не заглядывает вовсе. count(c.channel) считает строки, где значение не NULL: два оплаченных заказа пришли без канала, остаётся три. count(DISTINCT c.channel) берёт различные не-NULL значения, каналов всего два. count(DISTINCT o.user_id) по той же логике даёт число покупателей, а не число заказов.

Последняя форма спасает после соединений. Присоедини к заказам платежи, строки размножатся, и count(*) посчитает каждый заказ по нескольку раз, а count(DISTINCT o.id) вернёт честное число. Отдельная ловушка того же семейства живёт в LEFT JOIN: там count(*) даёт единицу даже клиенту вообще без заказов. Она разобрана в статье про LEFT JOIN.

Ошибка «column must appear in the GROUP BY clause»

Запрос: сколько заказов в каждом городе, и заодно показать имя клиента.

SELECT u.city, u.name, count(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.city;

ERROR:  column "u.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT u.city, u.name, count(o.id)
                       ^

Причина механическая. В группе «Казань» три строки, и u.name в них разный: дважды Борис, один раз Глеб. Группа схлопнется в одну строку, а какое из двух имён туда положить, база не знает. Выбирать наугад PostgreSQL отказывается.

Чинится тремя способами. Выбор между ними — это выбор смысла, а не синтаксиса. Дописать колонку в GROUP BY: группы станут мельче, разрез сменится с «по городу» на «по городу и клиенту». Обернуть в агрегат, min(u.name): ответ становится определённым, алфавитно первое имя в группе, для Казани это Борис. Либо сгруппировать по ключу, от которого колонка зависит однозначно:

SELECT u.id, u.name, u.city, count(o.id) AS orders_count
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY u.id;

u.id — первичный ключ, поэтому внутри группы name и city заведомо одинаковые. PostgreSQL выводит это из схемы и перечислять их не требует. Послабление держится именно на первичном ключе. Ограничение UNIQUE на users.name его не включает: группировка по имени возвращает ту же ошибку и снова требует u.city в GROUP BY.

ONLY_FULL_GROUP_BY: почему в MySQL тот же запрос может пройти

Тот же запрос с u.name вне GROUP BY на MySQL может и не упасть. Отвечает за это режим ONLY_FULL_GROUP_BY: в MySQL 8.4 он включён по умолчанию и отклоняет такие запросы ошибкой 1055. Но его можно выключить. Документация MySQL описывает последствия прямо: сервер «свободен выбрать любое значение из каждой группы», а раз значения различаются, выбранное недетерминировано. Там же оговорено, что ORDER BY на этот выбор не влияет, сортировка идёт уже после.

Практический перевод: запрос отработает, отчёт соберётся, а в строке «Казань» окажется то Борис, то Глеб, и в выводе на это ничего не намекнёт. В PostgreSQL такого переключателя нет, там запрос падает всегда. Легальный способ сказать «мне правда всё равно, какое значение» в MySQL — функция ANY_VALUE(): режим остаётся включённым, а колонка помечена явно.

GROUP BY 1: группировка по номеру колонки

SELECT u.city, count(*) AS orders_count, sum(o.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY 1
ORDER BY 1;

Единица — номер колонки в SELECT, нумерация с единицы. Документация PostgreSQL разрешает в GROUP BY «имя или порядковый номер выходной колонки», так что группировать по выражению можно и через алиас: в SELECT date_trunc('month', created_at) AS month … GROUP BY month длинный date_trunc написан один раз. Алиас выдержит правку SELECT, номер нет. MySQL принимает и номера, и алиасы. SQL Server не принимает ни того ни другого: его GROUP BY требует выражение над колонкой, а колонка обязана быть во FROM.

Ловушка вылезает на проде, когда готовый отчёт правят. Аналитик меняет разрез с города на клиента и трогает только первую строку:

SELECT u.name, count(*) AS orders_count, sum(o.amount) AS total

Запрос не сломался и ничего не сказал. Он просто стал считать другое: номер в GROUP BY молча переехал на новую колонку, за ним и ORDER BY 1. Стой там имя колонки, правка дала бы ошибку. Отсюда практика: GROUP BY 1 для разового запроса в консоли, в сохранённом отчёте — имя колонки.

WHERE или HAVING: куда ставить фильтр

Порядок выполнения фиксированный: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Перепутаешь порядок самих предложений в тексте запроса — получишь синтаксическую ошибку. WHERE работает на строках, которых группировка ещё не коснулась. HAVING работает на группах, где агрегаты уже посчитаны. Один и тот же порог «больше 1000», поставленный в разные места, отвечает на разные вопросы.

SELECT u.name, count(*) AS orders_count, round(avg(o.amount), 2) AS avg_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.amount > 1000
GROUP BY u.name
ORDER BY u.name;

Это ответ на «какой средний чек, если считать только крупные заказы». Мелкие заказы Ани на 900 и 800 выброшены до того, как посчиталось среднее, поэтому у неё остался один заказ вместо трёх. Глеб с единственным заказом на 500 исчез из выдачи целиком.

SELECT u.name, count(*) AS orders_count, round(avg(o.amount), 2) AS avg_amount
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.name
HAVING avg(o.amount) > 1000
ORDER BY u.name;

Это ответ на «у кого средний чек выше 1000». Все три заказа Ани учтены, включая мелкие, и среднее у неё вышло другое: 1366.67 против 2400. Отсеялась целая группа, Глеб со своими 500.

Оба запроса корректны. Неверный тот, который отвечает не на заданный вопрос: условие на значение отдельной строки идёт в WHERE, условие на результат агрегата идёт в HAVING. Агрегат в WHERE запрещён жёстко, а HAVING пускает к себе только то, что пережило группировку. Замени условие в HAVING на o.status = 'paid', и запрос не выполнится:

ERROR:  column "o.status" must appear in the GROUP BY clause or be used in an aggregate function

Пережили колонки из GROUP BY и агрегаты. Отдельной строки со статусом на этом этапе уже нет, спрашивать про неё нечего. А HAVING u.name = 'Аня' в том же запросе отработает: u.name стоит в GROUP BY, и группе он известен.

В одном запросе оба фильтра работают вместе, и обычно так и надо. Клиенты с тремя и более оплаченными заказами:

SELECT u.name, count(*) AS paid_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.name
HAVING count(*) >= 3
ORDER BY u.name;

WHERE убрал отменённый заказ Бориса, GROUP BY собрал оставшиеся по клиентам, HAVING оставил группы от трёх строк. Ключевое: count(*) в HAVING считает то, что осталось после WHERE. На этих шести заказах ответ без WHERE не изменится, но на живом объёме счётчик начнёт включать отменённые, и в постоянные покупатели попадут те, кто не оплатил ни разу.

Как GROUP BY поступает с NULL

SELECT c.channel, count(*) AS orders_count, round(avg(o.amount), 2) AS avg_amount
FROM orders o
JOIN order_channel c ON c.order_id = o.id
GROUP BY c.channel
ORDER BY c.channel;

Два заказа без канала не потерялись и не разошлись по своим группам, а собрались в одну. Для группировки все NULL считаются одинаковыми, хотя сравнение NULL = NULL истинным не бывает. В конце её держит ORDER BY: по умолчанию PostgreSQL сортирует NULL последними. Правило не про один PostgreSQL: в документации SQL Server записано то же самое, все NULL в группирующей колонке движок считает равными и собирает в одну группу.

Пустой заголовок в отчёте читается как сбой выгрузки, поэтому группу подписывают:

SELECT coalesce(c.channel, 'не определён') AS channel, count(*) AS orders_count
FROM orders o
JOIN order_channel c ON c.order_id = o.id
GROUP BY 1
ORDER BY 1;

coalesce стоит в SELECT, а GROUP BY 1 ссылается на готовую колонку. Здесь так же сработал бы и GROUP BY channel по алиасу.

Типичные ошибки

WHERE count(*) > 1. Попытка отфильтровать по счётчику там, где счётчика ещё нет:

ERROR:  aggregate functions are not allowed in WHERE

К моменту WHERE группы не собраны, считать нечего. Условие на агрегат всегда идёт в HAVING.

Алиас из SELECT внутри HAVING. Запрос ... GROUP BY status HAVING orders_count > 1 не выполнится:

ERROR:  column "orders_count" does not exist

Асимметрия неочевидная, зато задокументированная: имя выходной колонки разрешено в GROUP BY и ORDER BY, в HAVING его нет. ORDER BY orders_count DESC в том же запросе отработает, а HAVING требует повторить count(*) целиком.

Частые вопросы

Чем GROUP BY отличается от DISTINCT

SELECT city FROM users GROUP BY city и SELECT DISTINCT city FROM users вернут одно и то же: Казань и Москва. Пока агрегатов нет, GROUP BY работает как DISTINCT, и план часто получается одинаковый. Разница появляется, когда по группе надо что-то посчитать: DISTINCT этого не умеет, и HAVING к нему не прикрутить.

Можно ли писать HAVING без GROUP BY

Можно. Без GROUP BY вся выборка становится одной группой, и HAVING решает, показывать её или нет. SELECT count(*), sum(amount) FROM orders HAVING sum(amount) > 1000 вернёт одну строку: 6 заказов на 7000. Подними порог до 100000, и запрос отдаст ноль строк, а не строку с нулями. Приём редкий, читается как сторож в отчёте: выведи итог, только если он перевалил за порог.

Как посчитать оплаченные и отменённые одним запросом

Через FILTER: count(*) FILTER (WHERE o.status = 'paid') рядом с count(*) FILTER (WHERE o.status = 'cancelled') даст по клиентам 3 и 0 у Ани, 1 и 1 у Бориса. Переносимый вариант той же логики — sum(CASE WHEN o.status = 'paid' THEN 1 ELSE 0 END), он работает везде.

Где потренироваться

В пути «SQL для аналитиков» на Koddo этот же порядок разложен на задачи с автопроверкой. Пак называется «Агрегаты и группировка». Открывают его «Заказы по статусам»: первый GROUP BY, имя метрики через AS. Дальше «Средний чек по каналам», где фильтр в WHERE стоит до группировки, а round доводит сумму до копеек. Замыкают «Постоянные покупатели» с первым HAVING. В задачах по SQL с решениями на GROUP BY и HAVING держатся третья, пятая и восьмая.

Потренируйте фильтрацию групп в задаче на поиск повторных откликов: сначала исключите пустые email, затем посчитайте группы и оставьте только дубли.