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

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

Теория

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

Например необходимо узнать:

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

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

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

Основные агрегатные функции:

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

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

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

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

Синтаксис

SELECT COUNT(*)                      -- считаем количество строк в таблице
FROM workers;                        -- берём данные из таблицы workers

SELECT MAX(salary) - MIN(salary)     -- считаем разницу между максимальной и минимальной зарплатой
FROM workers;                        -- берём данные из таблицы 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) найти максимальную зарплату в отделе 2

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
• Попробуйте найти разницу между максимальной и минимальной зарплатой в отделе 3

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)

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

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

Практика

Проверь себя