Агрегатные функции

COUNT, SUM, AVG, MIN, MAX — количество, сумма, среднее

Теория

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

Например необходимо узнать:
• сколько сотрудников работает в компании;
• какая средняя зарплата у работников;
• у какого товара самая высокая цена;
• сколько всего заказов оформлено;
• какая минимальная и максимальная зарплата встречается в таблице.

Такого рода задачи ложатся на плечи агрегатных функций.

Агрегатная функция берёт набор строк и возвращает одно итоговое значение для этого набора. То есть она как будто «сворачивает» много строк в один результат. Например, из всех зарплат в таблице работников можно получить одно число — среднюю зарплату. Или из всех заказов — одно число: общее количество заказов.

Основные агрегатные функции:
• COUNT() — считает количество строк или значений в наборе;
• SUM() — складывает (суммирует) значения;
• AVG() — находит среднее значение;
• MIN() — показывает минимальное значение;
• MAX() — показывает максимальное значение.

Важно: агрегатные функции обычно не учитывают NULL. Если значение в столбце отсутствует, оно просто не участвует в расчёте. Например, AVG(salary) будет считать среднюю зарплату только по тем работникам, у которых зарплата указана.

Отдельно запомните конструкцию COUNT(*). Она считает строки целиком по таблице. А вот COUNT(column) считает только строки по указанному столбцу и при этом не учитывает значения NULL.

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

Синтаксис

SELECT COUNT(*)
FROM workers;

SELECT MAX(salary) - MIN(salary)
FROM workers;

SQL

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

Например, COUNT(salary) считает, в скольких строках столбца salary есть значение. Если в каких-то строках есть NULL, они в подсчёт не попадут.

Конструкция COUNT(*) работает иначе. Она считает не значения конкретного столбца, а просто количество строк по таблице.

Функции SUM(), AVG(), MIN() и MAX() работают с числовыми данными и возвращают одно итоговое число:
• SUM() — сумму;
• AVG() — среднее;
• MIN() — минимальное значение;
• MAX() — максимальное значение.

Результат агрегатных функций можно использовать в выражениях. Например, можно написать MAX(salary) - MIN(salary) и получить разницу между самой большой и самой маленькой зарплатой.

Примеры

1. Сколько всего работников в таблице

SELECT COUNT(*) AS total_workers
FROM workers;

Самый базовый пример. Запрос считает все строки в таблице workers и показывает общее количество строк (в данном случае работников).

🔄 Попробуйте изменить запрос:

• Поменяйте псевдоним total_workers на workers_count
• Попробуйте выполнить такой же запрос для таблицы orders

2. Сколько работников получают зарплату больше 90000

SELECT COUNT(*) AS high_salary_workers
FROM workers
WHERE salary > 90000;

Здесь используется знакомый оператор WHERE. Сначала SQL отбирает только работников с зарплатой больше 90000, а потом COUNT(*) считает, сколько таких строк получилось.

🔄 Попробуйте изменить запрос:

• Поменяйте условие на salary >= 100000
• Попробуйте узнать, сколько работников получают меньше 70000

3. Средняя зарплата работников из конкретного отдела

SELECT AVG(salary) AS avg_salary
FROM workers
WHERE department_id = 2;

Сначала выбираются только работники из отдела department_id = 2, а потом для них считается средняя зарплата.

🔄 Попробуйте изменить запрос:

• Поменяйте номер отдела на другой
• Попробуйте вместо AVG(salary) использовать MAX(salary)

4. Разница между COUNT(*) и COUNT(status)

SELECT COUNT(*) AS total_orders,
       COUNT(status) AS orders_with_status
FROM orders;

Самое интересное. COUNT(*) считает все строки таблицы orders. А COUNT(status) считает только те строки, где в столбце status есть значение. Если статус не указан и там стоит NULL, такая строка во второй подсчёт уже не попадёт.

🔄 Попробуйте изменить запрос:

• Замените status на order_date и сравните результат
• Попробуйте выполнить такой же запрос для таблицы users и сравнить COUNT(*) с COUNT(city)

5. Разница между максимальной и минимальной зарплатой

SELECT MAX(salary) - MIN(salary) AS salary_range
FROM workers;

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

🔄 Попробуйте изменить запрос:

• Поменяйте имя результата salary_range на diff_salary
• Попробуйте сделать такой же расчёт только для одного отдела через WHERE

6. Средняя стоимость двух товаров вместе

SELECT AVG(price * 2) AS avg_double_price
FROM products;

Этот пример показывает, что выражение можно использовать и внутри агрегатной функции. Сначала для каждой строки считается price * 2, а потом по результату находится среднее.

🔄 Попробуйте изменить запрос:

• Замените price * 2 на price + 100

7. Разница между COUNT(city) и COUNT(DISTINCT city)

SELECT COUNT(city) AS filled_cities,
       COUNT(DISTINCT city) AS unique_cities
FROM users;

Здесь как раз хорошо видно, зачем иногда нужен DISTINCT. COUNT(city) считает все непустые значения в столбце city, включая повторы. А COUNT(DISTINCT city) считает только разные города без повторений. Если, например, десять пользователей живут в Москве, в первом подсчёте все десять строк попадут в результат, а во втором Москва даст только одно уникальное значение.

🔄 Попробуйте изменить запрос:

• Уберите DISTINCT из второго выражения и сравните результат
• Попробуйте выполнить такой же запрос для status в таблице orders

Типичные ошибки

Путают COUNT(*) и COUNT(column)
COUNT(*) считает все строки таблицы. COUNT(salary) считает только строки, где в столбце salary есть не NULL значение.

Забывают, что WHERE срабатывает раньше агрегатной функции
Сначала SQL отбирает строки по условию, и только потом агрегатная функция считает итог по оставшимся данным.

Думают, что AVG() учитывает NULL как ноль
Нет, NULL просто исключается из расчёта.

Не используют арифметику с агрегатными функциями
Агрегаты можно комбинировать. Например, MAX(salary) - MIN(salary) — это нормальное и очень полезное выражение.

Пытаются применять SUM() и AVG() к текстовым столбцам
Эти функции предназначены для числовых данных.

Забывают добавить DISTINCT, когда нужно посчитать уникальные значения, и добавляют его, когда это не нужно
COUNT(department_id) считает все непустые значения столбца, включая повторы. Если нужно узнать, сколько разных отделов есть в таблице, надо писать COUNT(DISTINCT department_id). Но если ваша задача — посчитать все строки или все заполненные значения, DISTINCT только исказит результат.

Агрегатные функции

Теория

Синтаксис

Примеры

1. Сколько всего работников в таблице

🔄 Попробуйте изменить запрос:

2. Сколько работников получают зарплату больше 90000

🔄 Попробуйте изменить запрос:

3. Средняя зарплата работников из конкретного отдела

🔄 Попробуйте изменить запрос:

4. Разница между COUNT(*) и COUNT(status)

🔄 Попробуйте изменить запрос:

5. Разница между максимальной и минимальной зарплатой

🔄 Попробуйте изменить запрос:

6. Средняя стоимость двух товаров вместе

🔄 Попробуйте изменить запрос:

7. Разница между COUNT(city) и COUNT(DISTINCT city)

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя