Итоги по таблице: COUNT, SUM, AVG и пустые значения NULL

Одно число вместо списка

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

SELECT COUNT(*) FROM orders;

COUNT(*)
9

SELECT SUM(amount) FROM orders;

SUM(amount)
6170

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

SELECT SUM(amount) AS revenue FROM orders WHERE status <> 'отменён';

revenue
5280

Несколько агрегатов можно собрать в одну строку сводки — каждый со своим именем через AS:

SELECT ROUND(AVG(price), 2) AS avg_price,
       MIN(price) AS min_price,
       MAX(price) AS max_price
FROM books;

avg_price | min_price | max_price
690.0     | 450       | 1200

Округление

Среднее редко получается ровным. Средняя сумма всех заказов — 6170, делённое на 9, — длинная дробь 685,5555… Для отчёта её округляют функцией ROUND: первый аргумент — что округлить, второй — сколько знаков оставить после точки.

SELECT ROUND(AVG(amount), 2) AS avg_check FROM orders;

avg_check
685.56

Дробную часть SQL отделяет точкой, а не запятой: так записывают числа и в запросах, и в результатах.

NULL: значение неизвестно

У клиентки Дины не заполнен город. В базе на этом месте лежит NULL — особая отметка «значение неизвестно». Это не ноль и не пустая строка: про ноль мы знаем, что он ноль, а про NULL не знаем ничего.

Отсюда главная особенность: с NULL нельзя сравнивать знаком =. «Неизвестно равно неизвестно?» — ответ тоже «неизвестно», и строка в результат не попадает:

SELECT name FROM customers WHERE city = NULL;

name
(0 строк)

Для пустых значений есть специальные проверки IS NULL и IS NOT NULL:

SELECT name FROM customers WHERE city IS NULL;

name
Дина

Агрегаты и NULL

Агрегатные функции пропускают NULL. Это хорошо видно на COUNT:

SELECT COUNT(*) AS all_customers, COUNT(city) AS with_city FROM customers;

all_customers | with_city
6             | 5

COUNT(*) посчитал все строки, а COUNT(city) — только те, где город заполнен. Так же ведут себя SUM, AVG, MIN и MAX: пустые значения в расчёт не идут. Разница между двумя счётчиками — быстрый способ найти дыры в данных.

Чтобы в отчёте вместо NULL стояло понятное слово, используют COALESCE. Функция возвращает первое заполненное значение из списка:

SELECT name, COALESCE(city, 'не указан') AS city FROM customers;

name  | city
Анна  | Москва
Борис | Казань
Вера  | Москва
Глеб  | Пермь
Дина  | не указан
Егор  | Казань

Таблица при этом не изменилась: у Дины в базе по-прежнему NULL, а «не указан» существует только в ответе.

SELECT COUNT(*) AS orders_count,
       SUM(amount) AS revenue,
       ROUND(AVG(amount), 2) AS avg_check,
       MAX(amount) AS max_order
FROM orders
WHERE status = 'доставлен';

orders_count | revenue | avg_check | max_order
6            | 4260    | 710.0     | 1200