📊 Урок 3.2: GROUP BY

👋 В прошлом уроке мы считали что-то по ВСЕЙ таблице: общее количество товаров, среднюю цену. Но что, если нужно посчитать по каждой категории отдельно? Сколько товаров в «Электронике», сколько в «Посуде»? Тут на сцену выходит GROUP BY — он группирует строки по значению какого-то столбца и считает что-то внутри каждой группы.

🧩 Что такое GROUP BY?

Представь, что у тебя куча разноцветных носков, и ты хочешь узнать, сколько носков каждого цвета. Ты раскладываешь их по кучкам — синие к синим, красные к красным — а потом считаешь каждую кучку. Вот это и есть GROUP BY!

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

📦 Подготовим данные

Давай создадим несколько таблиц для практики. Сотрудники с зарплатами и товары в категориях.

-- Таблица сотрудников
CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, department TEXT, salary REAL, city TEXT
); INSERT INTO employees (id, name, department, salary, city) VALUES
(1, 'Анна', 'IT', 80000, 'Москва'),
(2, 'Борис', 'IT', 95000, 'Москва'),
(3, 'Виктор', 'Sales', 55000, 'СПб'),
(4, 'Галина', 'Sales', 48000, 'Москва'),
(5, 'Дмитрий', 'HR', 60000, 'СПб'),
(6, 'Елена', 'IT', 120000, 'Казань'),
(7, 'Жанна', 'HR', 45000, 'Москва'),
(8, 'Захар', 'Sales', 70000, 'Казань'),
(9, 'Ирина', 'IT', 110000, 'СПб'),
(10, 'Константин', NULL, 50000, 'Москва'); -- Таблица товаров (из прошлого урока, но пересоздадим)
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT, price REAL, quantity INTEGER
); INSERT INTO products (id, name, category, price, quantity) VALUES
(1, 'Ноутбук', 'Электроника', 75000, 10),
(2, 'Мышь', 'Электроника', 1500, 50),
(3, 'Клавиатура', 'Электроника', 3500, 30),
(4, 'Кружка', 'Посуда', 500, 100),
(5, 'Тарелка', 'Посуда', 300, 200),
(6, 'SQL-бестселлер', 'Книги', 1200, 15),
(7, 'Python для профи', 'Книги', 1500, 20),
(8, 'Наушники', 'Электроника', 4500, 15),
(9, 'Чайник', 'Посуда', 2500, 12),
(10, 'Стол', 'Мебель', 10000, 5);

👥 GROUP BY с COUNT — товары по категориям

Самый простой пример — посчитать, сколько товаров в каждой категории.

-- Сколько товаров в каждой категории?
SELECT category, COUNT(*) AS "Количество товаров"
FROM products
GROUP BY category;

Смотри, что произошло. Было 10 строк с разными категориями. SQL нашёл все уникальные значения category — их 4 — и сгруппировал строки по ним. Для каждой группы посчитал COUNT(*).

Без GROUP BY мы бы получили просто 10. С GROUP BY — разбивку по группам.

🏢 GROUP BY с SUM — зарплаты по отделам

Теперь посчитаем сумму зарплат по каждому отделу. Это уже похоже на настоящий отчёт для бухгалтерии!

-- Сумма зарплат по отделам
SELECT department, SUM(salary) AS "ФОТ (фонд оплаты труда)"
FROM employees
GROUP BY department;

Обрати внимание: Константин с department = NULL тоже попал в группировку? А вот и нет! GROUP BY игнорирует NULL или выделяет их в отдельную группу в зависимости от СУБД. В SQLite NULL-группа не показывается, а в PostgreSQL и MySQL покажется как отдельная строка с NULL.

Давай посчитаем среднюю зарплату по отделам:

-- Средняя зарплата по отделам
SELECT department, ROUND(AVG(salary), 0) AS "Средняя зарплата", COUNT(*) AS "Сотрудников"
FROM employees
WHERE department IS NOT NULL
GROUP BY department;

🎯 GROUP BY с несколькими столбцами

Можно группировать по нескольким столбцам сразу. Например, посчитать зарплаты по отделам и городам.

-- Средняя зарплата по отделам и городам
SELECT department, city, ROUND(AVG(salary), 0) AS "Средняя зарплата", COUNT(*) AS "Сотрудников"
FROM employees
WHERE department IS NOT NULL
GROUP BY department, city
ORDER BY department, city;

Теперь у нас 8 групп (3 отдела × несколько городов). Видно, что в IT средняя зарплата сильно различается по городам: в Казани 120 000, а в Москве 87 500.

🔍 HAVING — фильтр для групп

Помнишь, в прошлом уроке я говорил, что агрегатные функции нельзя использовать в WHERE? WHERE AVG(salary) > 50000 не сработает.

Для фильтрации групп есть HAVING. Он работает как WHERE, но после GROUP BY и с агрегатными функциями.

-- Какие отделы имеют среднюю зарплату больше 60 000?
SELECT department, ROUND(AVG(salary), 0) AS avg_salary, COUNT(*) AS employees_count
FROM employees
WHERE department IS NOT NULL
GROUP BY department
HAVING AVG(salary) > 60000;

Только IT-отдел прошёл фильтр! HR (52 500) и Sales (57 667) отсеялись.

⚔️ WHERE vs HAVING — разница

Давай посмотрим на полную картину. WHERE отсекает строки до того, как они попадут в группы. HAVING отсекает группы после агрегации.

-- 1. Сначала WHERE отсекает строки, где salary < 50000
-- 2. Потом GROUP BY группирует оставшиеся строки
-- 3. Потом HAVING отсекает группы, где средняя зарплата < 70000
SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE salary >= 50000 -- убираем «дешёвых» сотрудников
GROUP BY department
HAVING AVG(salary) >= 70000 -- оставляем только «дорогие» отделы
ORDER BY avg_salary DESC;

Разберём, что произошло:

• WHERE salary >= 50000 — удалил строки: Галина (48000), Жанна (45000), Константин (50000 остался, условие >= 50000 пропускает).
• GROUP BY department — сгруппировал оставшиеся строки по отделам.
• HAVING AVG(salary) >= 70000 — оставил только IT (101 250) и Sales (70 000). HR вылетел, потому что после WHERE остался только Дмитрий (60000), и средняя стала 60000, что меньше 70000.

📐 Пример: всё вместе

Давай сделаем полноценный анализ наших товаров по категориям:

-- Анализ товаров по категориям
SELECT category, COUNT(*) AS "Товаров", ROUND(AVG(price), 0) AS "Средняя цена", SUM(price * quantity) AS "Общая стоимость", MIN(price) AS "Мин. цена", MAX(price) AS "Макс. цена"
FROM products
WHERE price > 0
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY "Общая стоимость" DESC;

Категория «Мебель» не попала в результат, потому что HAVING COUNT(*) >= 2 — в ней всего 1 товар. Видишь, как HAVING отсек группы с малым количеством?

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

Это самая важная схема во всём модуле. Запомни её раз и навсегда:

SELECT category, COUNT(*) -- 5. Выбираем столбцы для вывода
FROM products -- 1. Откуда берём данные
WHERE price > 0 -- 2. Фильтруем строки (до группировки)
GROUP BY category -- 3. Группируем строки по категории
HAVING COUNT(*) >= 2 -- 4. Фильтруем группы (после группировки)
ORDER BY category; -- 6. Сортируем результат

Порядок выполнения (не путать с порядком написания!):

🍕 Пример с пиццерией — реальный бизнес-отчёт

Давай вернёмся к нашей пиццерии из прошлого урока. Сделаем группировку и получим настоящий бизнес-отчёт.

CREATE TABLE sales ( id INTEGER PRIMARY KEY, pizza_name TEXT, size TEXT, price REAL, quantity INTEGER, order_date DATE
); INSERT INTO sales VALUES
(1, 'Маргарита', 'Средняя', 450, 2, '2024-01-15'),
(2, 'Пепперони', 'Большая', 650, 1, '2024-01-15'),
(3, 'Маргарита', 'Большая', 550, 3, '2024-01-16'),
(4, 'Гавайская', 'Средняя', 500, 1, '2024-01-16'),
(5, 'Пепперони', 'Средняя', 500, 4, '2024-01-17'),
(6, 'Маргарита', 'Маленькая', 350, 2, '2024-01-17'),
(7, 'Четыре сыра', 'Большая', 700, 1, '2024-01-18'),
(8, 'Гавайская', 'Большая', 600, 2, '2024-01-18'),
(9, 'Маргарита', 'Средняя', 450, 3, '2024-01-19'),
(10, 'Пепперони', 'Большая', 650, 2, '2024-01-19'); -- Отчёт по видам пицц: сколько продали и на какую сумму
SELECT pizza_name, COUNT(*) AS "Количество заказов", SUM(quantity) AS "Всего штук", SUM(price * quantity) AS "Выручка", ROUND(AVG(price), 2) AS "Средняя цена"
FROM sales
WHERE pizza_name IS NOT NULL
GROUP BY pizza_name
ORDER BY "Выручка" DESC;

Какой вывод? Маргарита — королева пиццерии! Больше всего заказов, больше всего штук, максимальная выручка. Четыре сыра — редкий, но дорогой гость.

А теперь добавим фильтры:

-- Какие пиццы принесли больше 2000 рублей?
SELECT pizza_name, SUM(price * quantity) AS total_revenue, SUM(quantity) AS total_sold
FROM sales
GROUP BY pizza_name
HAVING total_revenue > 2000
ORDER BY total_revenue DESC;

Гавайская и Четыре сыра принесли меньше 2000 — HAVING их отсеял.

⚠️ Частые ошибки

❌ Ошибка 1: Забыли GROUP BY при использовании агрегатов с обычными столбцами

-- Ошибка! В SELECT есть name (не агрегат) и COUNT(*) (агрегат)
SELECT name, COUNT(*)
FROM products;
-- ❌ SQLite может это выполнить, но результат будет странным
-- В других СУБД — ошибка -- Правильно: убрать name или добавить GROUP BY
SELECT category, COUNT(*) FROM products GROUP BY category;

❌ Ошибка 2: Использование WHERE вместо HAVING

-- Ошибка! WHERE не умеет в агрегаты
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000 -- ❌ Ошибка!
GROUP BY department; -- Правильно: HAVING
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

❌ Ошибка 3: Путаница с порядком

Новички часто пишут: WHERE → GROUP BY → HAVING → ORDER BY (правильно), но иногда пытаются поставить HAVING перед GROUP BY — это ошибка.

🏋️ Практические задания

SELECT city, COUNT(*) AS employees_count
FROM employees
GROUP BY city;

SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE department IS NOT NULL
GROUP BY department
ORDER BY avg_salary DESC;

SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE department IS NOT NULL
GROUP BY department
HAVING AVG(salary) > 50000;

SELECT category, COUNT(*) AS product_count, ROUND(AVG(price), 0) AS avg_price, SUM(price * quantity) AS total_value
FROM products
GROUP BY category;

SELECT category, ROUND(AVG(price), 0) AS avg_price, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING AVG(price) > 1000
ORDER BY avg_price DESC;

SELECT size, SUM(quantity) AS "Всего штук", SUM(price * quantity) AS "Выручка"
FROM sales
GROUP BY size
ORDER BY "Выручка" DESC;

📋 Шпаргалка: GROUP BY и HAVING

🚀 Что дальше?

Мы научились группировать данные и считать по группам. Это уже серьёзный уровень! В следующем модуле (4.1) мы узнаем, как соединять таблицы — ведь данные редко лежат в одной таблице. Заказы хранятся отдельно, клиенты — отдельно. JOIN поможет нам собрать всё вместе. Увидимся там! 🔗

Урок 3.2 — GROUP BY | Модуль 3: Считаем и группируем