Агрегатные функции SQL и группировка

Агрегатные функции SQL помогают получать итоги по данным таблицы. Например, посчитать количество товаров, найти среднюю цену или сложить цены товаров в наборе.

COUNT считает строки, SUM складывает значения, AVG вычисляет среднее, а MIN и MAX находят минимум и максимум.

В примерах будем работать с таблицей конфет candy. Цена указана в поле price, наличие начинки — в поле filling.

• COUNT
• SUM, AVG, MIN, MAX
• GROUP BY: группировка строк
• HAVING: отбор групп по условию

Таблица candy

При включённом фильтре «С начинкой» каталог показывает три позиции. Проверим количество позиций и сколько из них имеют цену.

После AS можно задать заголовок столбца результата. Здесь candies — количество конфет, prices — количество указанных цен.

SELECT COUNT(*) AS candies,
       COUNT(price) AS prices
FROM candy
WHERE filling = true;

У всех трёх конфет с начинкой указана цена. Если убрать WHERE, счётчик покажет все семь позиций каталога.

Рассчитаем сумму цен конфет с начинкой, а также их среднюю, минимальную и максимальную цену.

SELECT SUM(price) AS total,
       AVG(price) AS mean_price,
       MIN(price) AS lowest,
       MAX(price) AS highest
FROM candy
WHERE filling = true;

Цены конфет с начинкой — 30, 60 и 90. Вместе это 180, в среднем 180 / 3 = 60 за конфету. Крайние значения — 30 и 90.

В отчёте два итога — для конфет с начинкой и без неё. Чтобы сверить оба одним запросом, сгруппируем строки по filling.

GROUP BY собирает вместе строки с одинаковым значением поля. SUM вычисляет сумму отдельно для каждой группы, а filling в результате показывает, к какой группе она относится.

В этом примере filling указан дважды. В GROUP BY он разделяет конфеты по наличию начинки, а в SELECT показывает, к какой группе относится сумма.

SELECT filling, SUM(price) AS total
FROM candy
GROUP BY filling;

Три конфеты с начинкой стоят вместе 180, четыре без начинки — 170. Сумма двух строк отчёта должна совпасть с суммой по всему каталогу.

В каталоге ищем конфеты, у которых совпадают цена и признак начинки. Выведем только сочетания, встретившиеся больше одного раза.

Перечислите в GROUP BY оба поля через запятую. Теперь каждая группа — одно сочетание filling и price. COUNT(*) покажет, сколько в ней строк.

HAVING отбирает группы по условию. В этом примере оставим только группы, в которых две конфеты или больше.

SELECT filling, price, COUNT(*) AS qty
FROM candy
GROUP BY filling, price
HAVING COUNT(*) > 1;

У Pluto's Tears и Mint Vortex цена 40, обе конфеты без начинки. Для других сочетаний в таблице есть только по одной строке.