← назад к разделу

SQL С НУЛЯ Подсчёты по группам

Агрегаты: COUNT, GROUP BY и HAVING

Java · Go · Node · Python

Как посчитать строки, клиентов и выручку, не потерять пустые группы и не перепутать фильтр записей с фильтром итогов.

1. Агрегат превращает набор строк в значение

1 / 5

COUNT считает, SUM складывает, AVG находит среднее, MIN и MAX - границы. Один запрос может вернуть сразу несколько показателей.

  • COUNT(*) считает все строки.
  • COUNT(paid_at) пропускает NULL.
  • COUNT(DISTINCT customer_id) отвечает, сколько разных клиентов купили.
23 заказаcus-01: 7другие: 167 покупателейCOUNT(*) = 23COUNT(DISTINCT customer_id) = 7«Сколько покупок» и «сколько покупателей» - разные вопросы

2. GROUP BY считает каждую корзину отдельно

2 / 5

Группировка по status собирает заказы с одинаковым статусом и выдаёт по строке итога на каждый. В SELECT допустимы колонки группировки и агрегаты.

  • total_amount без агрегата рядом с GROUP BY status не имеет одного значения.
  • Несколько колонок в GROUP BY создают более мелкие группы.
  • Порядок итогов снова задаёт только ORDER BY.
PAID · 4990PAID · 6990GROUP BYstatusPAID2 · 11980Одна группа - одна строка результата

3. Покупателей без заказов возвращает LEFT JOIN

3 / 5

Группировка только по orders не создаст группу для человека без покупок. Полный список берут из customer, заказы присоединяют через LEFT JOIN и считают COUNT(o.id).

  • COUNT(*) дал бы единицу на строку с пустым заказом.
  • COUNT(o.id) пропускает NULL и честно даёт ноль.
  • Этот приём нужен для любых пустых групп в отчёте.
Olga · cus-07o.idNULLCOUNT = 0COUNT(*) здесь ошибочно дал бы 1

4. WHERE отбирает строки, HAVING - группы

4 / 5

До группировки результата COUNT ещё нет, поэтому условие на него пишут в HAVING. Обычные фильтры лучше оставить в WHERE: тогда базе достаётся меньше строк.

  • WHERE status = 'PAID' сначала оставляет оплаченные.
  • GROUP BY customer_id собирает их по клиентам.
  • HAVING SUM(total_amount) > 10000 оставляет крупные итоги.
WHEREстрокиGROUP BYкорзиныHAVINGитогиФильтр до подсчёта и фильтр после него

5. Точность и дубли важнее красивого числа

5 / 5

Деньги хранят в NUMERIC, а округляют только результат для показа. SUM, AVG, MIN и MAX игнорируют NULL; сумма пустого набора - NULL, не ноль.

  • round(avg(total_amount), 2) округляет средний чек на выводе.
  • Округление до суммирования меняет итог.
  • После JOIN проверяют, не размножились ли строки перед агрегатом.
0.10 + 0.10NUMERICSUM = 0.20точный расчётround0.20Округление - последний шаг, только для показа

До сих пор мы разглядывали строки. Но половина реальных вопросов — не «покажи», а «посчитай»: сколько заказов, на какую сумму, какой средний чек. Для этого есть агрегатные функции — они схлопывают множество строк в одно число.

SELECT seller_id, count(*), sum(total_amount) … GROUP BY seller_id sel-01 · 4990 sel-01 · 990 sel-02 · 890 sel-02 · 12990 sel-01 · 2 · 5980 sel-02 · 2 · 13880 одна корзина — одна строка итога колонка не из GROUP BY и не под агрегатом в выдачу не попадёт

GROUP BY раскладывает строки по корзинам, а count и sum превращают каждую корзину в одну строку итога.

Пять функций

живой пример

SELECT COUNT(*) FROM orders WHERE status = 'PAID';
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

COUNT(*) — сколько строк. Остальные четыре работают со значениями столбца:

  • SUM(total_amount) — сумма;
  • AVG(total_amount) — среднее;
  • MIN(total_amount) / MAX(total_amount) — минимум и максимум.

Можно несколько сразу:

живой пример

SELECT COUNT(*), SUM(total_amount), AVG(total_amount) FROM orders WHERE status = 'PAID';
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Один нюанс, который отличает профессионала: COUNT(*) считает строки, а COUNT(total_amount) — строки, где total_amount не пуст (не NULL). На чистых данных числа совпадут, на грязных — разойдутся, и эта разница сама по себе находка. А COUNT(DISTINCT customer_id) считает уникальные значения: не «сколько заказов», а «сколько разных клиентов заказывали».

живой пример

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM orders;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Двадцать три и семь: первое число отвечает «сколько раз купили», второе — «сколько человек купили». Путать их в отчёте дорого, а выглядят они одинаково убедительно. Тем же приёмом считают «сколько разных товаров продано» (COUNT(DISTINCT product_id) по позициям заказов) и «за сколько разных дней были заказы».

Пустые значения пропускает не один COUNT: SUM, AVG, MIN и MAX тоже считают так, будто строк с NULL нет вовсе. Отсюда две неожиданности в отчётах. Среднее делится не на все строки, а только на заполненные — и потому выходит выше, чем ждали. А SUM по набору, где не осталось ни одной строки, возвращает не ноль, а NULL: увидели в отчёте пустую клетку вместо нуля — причина, скорее всего, здесь.

Отработать здесь: Разброс цен на витрине

GROUP BY: посчитать по группам

Одно число на всю таблицу — редко то, что нужно. Чаще нужно «в разрезе»: сколько заказов в каждом статусе. Это делает GROUP BY:

живой пример

SELECT status, COUNT(*), SUM(total_amount)
FROM orders
GROUP BY status;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Вот они в действии:

status          | count | sum
PAID            | 4     | 23920.00
SHIPPED         | 2     | 12480.00
REFUNDED        | 1     |  6990.00
DELIVERED       | 3     | 21930.00
DISPUTE         | 1     |  5480.00
COMPLETED       | 5     | 33440.00
EXPIRED         | 1     | 12480.00
PENDING_PAYMENT | 2     | 16970.00
CANCELLED       | 4     |  8680.00

База разложила строки по кучкам с одинаковым status и посчитала каждую кучку отдельно — девять статусов, девять строк итога. Порядок строк здесь такой, какой отдала база: ORDER BY в запросе нет, и в другой раз статусы могут выстроиться иначе. Нужен предсказуемый порядок — допишите ORDER BY status или ORDER BY count(*) DESC. Группировать можно по нескольким столбцам: GROUP BY status, customer_id — «по каждому статусу, а внутри него по каждому клиенту».

Главное правило GROUP BY: в SELECT могут стоять только столбцы из GROUP BY и агрегатные функции. Запрос SELECT status, total_amount … GROUP BY status не выполнится — база резонно спросит: «в кучке „PAID" два разных total_amount, который из них показывать?»

Одно послабление в PostgreSQL всё же есть: если группировать по первичному ключу таблицы, её остальные колонки в SELECT писать можно. Угадывать тут нечего — на один c.id приходится ровно одна фамилия, база это знает и не спорит:

живой пример

SELECT c.id, c.last_name, COUNT(*)
FROM customer c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Во всех остальных случаях правило действует буквально, так что привыкать лучше к нему.

Чего в результате не будет — групп, которых нет в данных. GROUP BY customer_id по таблице заказов покажет только тех, у кого заказы есть: cus-07 и cus-08 не появятся нулём, они не появятся вовсе. Это самая частая причина жалобы «в отчёте кого-то не хватает», и ошибка молчаливая — строка просто отсутствует, а не помечена. Чтобы в выдаче были все, группируют от той таблицы, где лежит полный список, а вторую присоединяют через LEFT JOIN:

живой пример

SELECT c.id, c.last_name, COUNT(o.id) AS orders_count
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.last_name
ORDER BY orders_count;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Здесь важна деталь: COUNT(o.id), а не COUNT(*). Звёздочка считает строки, а у покупателя без заказов строка после LEFT JOIN всё равно одна — с пустыми колонками заказа, — и он получил бы в отчёте единицу вместо нуля. COUNT по колонке пустые значения пропускает и честно даёт ноль.

Отработать здесь: Заказы и выручка по продавцам · Заказы в разрезе статусов

HAVING: фильтр после подсчёта

Напишите WHERE COUNT(*) > 5, и PostgreSQL откажет: aggregate functions are not allowed in WHERE. WHERE фильтрует строки до группировки, а считать ещё нечего. А если условие — на сам результат подсчёта («покажи клиентов, у которых больше пяти заказов»)? Для этого есть HAVING — фильтр после подсчёта:

живой пример

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Они спокойно живут вместе, и запомнить просто: WHERE — про строки, HAVING — про группы. Например, WHERE status = 'PAID' сначала оставит оплаченные, GROUP BY customer_id сгруппирует, HAVING SUM(total_amount) > 10000 оставит только крупных клиентов.

FROM строки таблиц WHERE фильтр строк GROUP BY строки в группы HAVING фильтр групп ORDER BY порядок выдачи

Порядок выполнения запроса: WHERE отсекает строки до группировки, HAVING отбирает уже посчитанные группы.

В HAVING живёт не только COUNT. Условие «клиенты, которые принесли больше двадцати тысяч» — это сумма:

живой пример

SELECT customer_id, SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('PAID', 'COMPLETED', 'DELIVERED')
GROUP BY customer_id
HAVING SUM(total_amount) > 20000
ORDER BY revenue DESC;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Обратите внимание, что оба фильтра в одном запросе делают разную работу, и не только по смыслу. WHERE отсекает строки до группировки, то есть до самой тяжёлой части — базе остаётся считать меньше. HAVING фильтрует уже посчитанные группы. Поэтому условие, которое можно записать в WHERE, туда и записывают: при группировке по статусу HAVING status = 'PAID' формально сработает, но база сначала посчитает все девять групп и только потом выбросит восемь. В HAVING оставляют то, что без подсчёта не проверить.

Отработать здесь: Покупатели, которые заказывали больше двух раз

Деньги считают в NUMERIC, округляют в конце

AVG(total_amount) в примерах выше возвращает что-то вроде 2843.3333333333333333: шестнадцать знаков после запятой, потому что сумма в песочнице хранится как numeric, точный десятичный тип, и среднее считается точно. Это правильно, и это единственный тип для денег. float и double precision хранят двоичную дробь, в которой 0.1 не представляется точно: сумма тысячи чеков по 0.10 даёт 99.9999999999986, а отчёт за месяц расходится с бухгалтерией на копейки, которые никто не может найти.

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

живой пример

SELECT customer_id, round(avg(total_amount), 2) AS avg_check, sum(total_amount) AS total
FROM orders
GROUP BY customer_id;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

round(x, 2) работает с numeric; для double precision его нет, и запрос упадёт с «function round(double precision, integer) does not exist», это первый признак, что колонка объявлена не тем типом. Округлять до суммирования нельзя: сумма округлённых слагаемых отличается от округлённой суммы, и отчёт «по позициям» перестанет сходиться с отчётом «по заказам».

Где это применяется

Агрегаты — это сверка миров: интерфейс говорит «у вас 17 заказов» — COUNT(*) по базе обязан сказать то же самое; отчёт насчитал выручку — SUM её подтверждает или опровергает. Плюс поиск аномалий одной строкой: GROUP BY email HAVING COUNT(*) > 1 находит дубли пользователей, MIN(created_at) отвечает «с какого года у нас данные», сравнение COUNT(*) и COUNT(email) мгновенно показывает, у скольких записей не заполнена почта.

Где спотыкаются начинающие:

  • Ставят агрегат в WHERE (WHERE COUNT(*) > 5) — ошибка: условия на результат подсчёта живут в HAVING.
  • Смешивают в SELECT агрегаты и «просто столбцы», которых нет в GROUP BY. База честно отказывается угадывать, какое из многих значений показать.
  • Считают по задвоенным строкам после JOIN. Сначала убедитесь, что соединение не размножило строки, потом считайте — иначе SUM и COUNT врут в разы.

Коротко

  • Пять функций: COUNT, SUM, AVG, MIN, MAX; COUNT(*) считает строки, COUNT(колонка) только не-NULL.
  • GROUP BY схлопывает строки в группы: в SELECT могут быть только колонки группировки и агрегаты.
  • WHERE фильтрует строки до подсчёта, HAVING группы после него.
  • Деньги хранят в numeric, не во float; округляют round(x, 2) только на выводе.
  • COUNT(DISTINCT …) отвечает на «сколько разных», а не «сколько строк»: заказов двадцать три, покупателей семь.
  • Групп, которых нет в данных, в отчёте не будет: чтобы показать и пустых, группируют от полного списка через LEFT JOIN и считают COUNT(колонка), а не COUNT(*).

Что почитать дальше