GROUP BY и SUM в MySQL: считаем продажи билетов

Часть 7 из 16

Теперь расписание показывает событие, площадку и артиста в одной строке. Организатору нужны ещё итоги: сколько слотов у каждой сцены и сколько денег принесла продажа билетов. Добавим таблицу продаж и научимся группировать строки, считать количество и складывать суммы.

Здесь есть ловушка: если соединить билеты с несколькими выступлениями до подсчёта, одна продажа может попасть в сумму несколько раз. Разберём это на понятном примере и научимся проверять, что итог действительно верен.

Считаем слоты

SELECT e.event_id,e.name,COUNT(p.performance_id) AS slots
FROM events AS e
LEFT JOIN performances AS p ON p.event_id=e.event_id
GROUP BY e.event_id,e.name;

COUNT по ключу правой таблицы возвращает ноль для события без слотов; COUNT(*) посчитал бы само событие.

Создаём таблицу продаж

CREATE TABLE ticket_sales (
 sale_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 event_id BIGINT UNSIGNED NOT NULL,
 ticket_code CHAR(12) NOT NULL UNIQUE,
 ticket_type VARCHAR(30) NOT NULL,
 price DECIMAL(8,2) NOT NULL,
 sold_at DATETIME NOT NULL,
 checked_in_at DATETIME NULL,
 PRIMARY KEY (sale_id),
 CHECK(price >= 0),
 FOREIGN KEY(event_id) REFERENCES events(event_id)
) ENGINE=InnoDB;

Заполните её несколькими вымышленными строками с разными кодами и существующими event_id.

Считаем продажи отдельно

SELECT event_id,COUNT(*) AS tickets_sold,
 SUM(price) AS revenue,AVG(price) AS average_price
FROM ticket_sales
GROUP BY event_id
HAVING COUNT(*) >= 1
ORDER BY revenue DESC;

WHERE фильтрует строки до группировки, HAVING — готовые группы. Если соединить продажи с тремя выступлениями события до SUM, сумма билетов может утроиться. Следом оформим промежуточные расчёты через CTE.

Что происходит во время группировки

Без GROUP BY запрос по ticket_sales возвращает каждую продажу. С группировкой по event_id сервер собирает продажи одного события в группу и вычисляет для неё итог. COUNT(*) считает строки, SUM(price) складывает цены, AVG(price) вычисляет среднюю.

Если билетов нет, группы нет вообще; чтобы показать и такие события, позже присоединим агрегат к events через LEFT JOIN.

WHERE и HAVING — на разных этапах

Представьте сначала отбор отдельных билетов по дате, а затем подсчёт. Условие на дату ставьте в WHERE — оно удалит строки до подсчёта. Если нужно оставить только события, где продано минимум 100 билетов, пишите HAVING COUNT(*) >= 100: это условие относится к готовой группе.

Проверяем, не раздулся ли итог

Если на событие продано два билета по 100 рублей и у него три слота, соединение продаж со слотами создаст шесть строк. SUM увидит каждый билет трижды и покажет 600 вместо 200. Сначала агрегируйте ticket_sales отдельно, а затем соединяйте получившуюся строку с событием.

Практика: вручную проверьте одну группу из двух билетов и сравните итог запроса с калькулятором.

Разбираем итог на маленьком наборе

Допустим, в таблице есть три строки для события 1: цены 1000, 1000 и 1500 рублей. Тогда COUNT(*) вернёт 3, SUM(price) — 3500, а AVG(price) — 1166.67 после округления отображения. Добавьте в тестовую таблицу несколько продаж и вручную пересчитайте одну группу.

Если итог отличается, сначала проверьте входящие строки, затем условие GROUP BY.

Обратите внимание: агрегатные функции игнорируют NULL для аргумента, кроме COUNT(*), который считает строки целиком. Наш столбец цены объявлен NOT NULL, поэтому сумма не теряет строку из-за пустой цены. В другой модели нужно заранее решить, что означает неизвестное значение.

Когда не нужно считать COUNT DISTINCT

Иногда пытаются исправить размноженный JOIN командой COUNT(DISTINCT sale_id). Это может сделать конкретный счётчик правильным, но не спасает сумму: повторившиеся цены всё равно сложатся несколько раз. Надёжнее построить правильный набор и агрегировать каждую сущность на своём уровне, а не маскировать неверную связь.

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

Какой агрегат выбрать?

WHERE отбирает билеты до подсчёта, а HAVING отбирает уже посчитанные группы. Так проще проверить, на каком этапе должно действовать условие.

← Предыдущая статья: JOIN в MySQL: связываем артистов, события и площадки

Следующая статья: Подзапросы и CTE MySQL: строим отчёт по выручке →