Урок 5. Подсчёт и группировка — COUNT, SUM, AVG, GROUP BY, HAVING

Часто нужна не сама таблица, а итог по ней: сколько товаров, на какую сумму, какая средняя цена. Для этого в SQL есть агрегатные функции — они превращают много строк в одно число. Запросы урока — к таблице products из третьего урока.

Итоги по всей таблице

SELECT COUNT(*)   AS products,
       SUM(qty)   AS items,
       MIN(price) AS min_price,
       MAX(price) AS max_price,
       AVG(price) AS avg_price
FROM products;

Аргументом может быть и выражение. Стоимость всего склада:

SELECT SUM(price * qty) AS stock_value FROM products;

Агрегатные функции работают и с WHERE — тогда считаются только отобранные строки: SELECT COUNT(*) FROM products WHERE category = 'Канцелярия'; вернёт 3.

Округление и деление

Среднее редко бывает круглым. ROUND(x, n) округляет до n знаков после запятой: ROUND(AVG(price), 1) даст 38.4.

Осторожно с делением. В SQLite и PostgreSQL целое, делённое на целое, — тоже целое, остаток отбрасывается:

SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7 % 2 AS c;

Поэтому SUM(price) / COUNT(*) для этой таблицы даст 38, а не 38.375. Чтобы деление было дробным, одно из чисел должно быть дробным: SUM(price) * 1.0 / COUNT(*). В MySQL 7 / 2 сразу даёт 3.5000, а целое деление там пишут 7 DIV 2.

Группы: GROUP BY

GROUP BY раскладывает строки по группам с одинаковым значением, и агрегатные функции считаются в каждой группе отдельно. Сколько товаров и штук в каждой категории:

SELECT category, COUNT(*) AS products, SUM(qty) AS items
FROM products
GROUP BY category
ORDER BY category;

Восемь строк превратились в четыре — по одной на категорию. Самый дешёвый и самый дорогой товар в каждой:

SELECT category, MIN(price) AS cheapest, MAX(price) AS dearest
FROM products
GROUP BY category
ORDER BY category;

Группировать можно и по нескольким столбцам: GROUP BY category, price — группа на каждую пару значений.

Что можно писать в SELECT с GROUP BY

В ответе одна строка на группу. Поэтому в SELECT допустимы только столбцы из GROUP BY и агрегатные функции. Запрос «категория и название товара» не имеет смысла: в группе «Канцелярия» три названия, какое из них показать?

SELECT category, name, COUNT(*) FROM products GROUP BY category;

PostgreSQL на такой запрос отвечает ошибкой column "products.name" must appear in the GROUP BY clause or be used in an aggregate function. MySQL в обычном режиме (ONLY_FULL_GROUP_BY, включён по умолчанию) — тоже ошибкой. SQLite запрос выполнит и подставит название из какой-то строки группы — ошибки нет только с виду. Не полагайтесь на это: такой запрос не перенесётся на другую СУБД. Как правильно найти, например, самый дорогой товар каждой категории, рассказано в уроке Подзапросы.

Отбор групп: HAVING

WHERE отбирает строки до группировки и ничего не знает об итогах. Для условий на итоги есть HAVING — он отбирает уже готовые группы:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

Учёба с одним товаром отсеялась. Попытка написать то же через WHERE COUNT(*) >= 2 закончится ошибкой: в SQLite — misuse of aggregate: COUNT(), в MySQL — Invalid use of group function.

WHERE и HAVING часто нужны вместе. Категории, где не меньше двух товаров дешевле 50 рублей:

SELECT category, COUNT(*) AS cheap
FROM products
WHERE price < 50
GROUP BY category
HAVING COUNT(*) >= 2;

Сначала WHERE оставил пять товаров дешевле 50, затем GROUP BY разложил их по категориям, затем HAVING оставил группы с двумя и более товарами. Подробнее о разнице — справка Чем WHERE отличается от HAVING.

Порядок выполнения

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

1. FROM — берётся таблица;
2. WHERE — отбираются строки;
3. GROUP BY — строки раскладываются по группам;
4. HAVING — отбираются группы;
5. SELECT — считаются столбцы ответа и псевдонимы;
6. ORDER BY — ответ сортируется;
7. LIMIT — отрезаются первые строки.

Отсюда правило: в ORDER BY можно сослаться на псевдоним из SELECT, а в WHERE — нельзя (в PostgreSQL и MySQL это ошибка), ведь WHERE выполняется раньше. Две категории с самым дорогим складом:

SELECT category, SUM(price * qty) AS stock_value
FROM products
GROUP BY category
ORDER BY stock_value DESC
LIMIT 2;

NULL в итогах

Агрегатные функции пропускают NULL: SUM и AVG считают только заполненные значения, COUNT(столбец) — только непустые строки. COUNT(*) считает все строки. Если в группе нет ни одного значения, SUM вернёт NULL, а не 0. Подробнее — в справках Как посчитать строки и NULL в SQL.

Коротко

• COUNT, SUM, AVG, MIN, MAX превращают много строк в одно значение.
• GROUP BY считает итоги отдельно для каждой группы.
• В SELECT с GROUP BY — только столбцы группировки и агрегатные функции.
• WHERE отбирает строки до группировки, HAVING — группы после неё.
• В SQLite и PostgreSQL 7 / 2 = 3: для дробного ответа нужно дробное число.

Проверьте себя