---

Лабораторная работа «Агрегатные функции»
1. Теоретическая часть
Агрегатные функции выполняют вычисление над набором строк и
возвращают одно значение. В отличие от обычных функций, которые
работают с каждой строкой отдельно, агрегатные «схлопывают» множество
строк в одну.
Функция Описание Пример
COUNT(*)
Подсчёт количества строк в
группе COUNT(*) → 15
COUNT(column)
Подсчёт не-NULL
значений в столбце COUNT(phone) → 12
COUNT(DISTINCT
column)
Подсчёт уникальных
значений
COUNT(DISTINCT city)
→ 5
SUM(column) Сумма значений в столбце SUM(price) → 12500.50
AVG(column) Среднее арифметическое AVG(price) → 250.10
MIN(column) Минимальное значение MIN(price) → 99.00
MAX(column) Максимальное значение MAX(price) → 1500.00
Оператор GROUP BY
GROUP BY разбивает все строки таблицы на группы по указанным
столбцам. К каждой группе затем можно применить агрегатные функции.
Синтаксис:
SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2
Фильтрация групп: HAVING
HAVING используется для фильтрации уже сгруппированных данных.
WHERE применяется до группировки, а HAVING — после.
Синтаксис:
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 500;
2. Пример выполнения задания.
Задание 1. Простые агрегатные функции без группировки.
1.1Посчитайте общее количество книг в таблице books.
SELECT COUNT(*) AS total_books FROM books;
1.2. Найдите самую дорогую книгу в магазине (только цену).
SELECT MAX(price) AS max_price FROM books;
1.3. Найдите самую дешёвую книгу (только цену).
SELECT MIN(price) AS min_price FROM books;
1.4. Посчитайте среднюю цену всех книг. Округлите результат до двух
знаков после запятой.
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;
1.5. Посчитайте суммарную стоимость всех книг, имеющихся в наличии
(цена × количество на складе).
SELECT SUM(price * stock_quantity) AS total_stock_value FROM books;
Задание 2. Группировка по одному полю.
2.1. Посчитайте количество книг в каждом жанре. Выведите жанр и
количество.
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;
2.2. Для каждого автора найдите минимальную и максимальную цену на
его книги. Выведите имя автора, минимальную и максимальную цену.
SELECT a.name, MIN(b.price) AS min_price, MAX(b.price) AS max_price
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name;
2.3. Посчитайте общее количество страниц, которое написал каждый
автор (сумма по всем его книгам). Выведите имя автора и общее
количество страниц. Отсортируйте по убыванию количества страниц.
SELECT a.name, SUM(b.pages) AS total_pages
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name
ORDER BY total_pages DESC;
2.4. Найдите средний год публикации книг для каждой страны автора.
Выведите страну и средний год (без дробной части).
SELECT a.country, FLOOR(AVG(b.publication_year)) AS avg_year
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.country;
Задание 3: Группировка по нескольким полям
3.1. Посчитайте количество книг в разрезе «жанр — страна автора».
Выведите жанр, страну и количество книг.
SELECT b.genre, a.country, COUNT(*) AS book_count
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY b.genre, a.country
ORDER BY b.genre, a.country;
3.2. Для каждого автора и каждого жанра посчитайте суммарную
стоимость книг на складе (цена × количество). Выведите имя автора,
жанр и суммарную стоимость. Отсортируйте по автору, затем по
убыванию суммарной стоимости.
SELECT a.name, b.genre, SUM(b.price * b.stock_quantity) AS total_value
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name, b.genre
ORDER BY a.name, total_value DESC;
Задание 4: Фильтрация групп с HAVING
4.1. Найдите авторов, у которых средняя цена книги превышает 700
рублей. Выведите имя автора и среднюю цену.
SELECT a.name, AVG(b.price) AS avg_price
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name
HAVING AVG(b.price) > 700;
4.2. Найдите жанры, в которых более двух книг. Выведите жанр и
количество книг.
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING COUNT(*) > 2;
4.3. Найдите авторов, у которых суммарное количество страниц по всем
книгам больше 1500. Выведите имя автора и суммарное количество
страниц.
SELECT a.name, SUM(b.pages) AS total_pages
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name
HAVING SUM(b.pages) > 1500;
4.4. Найдите жанры, в которых самая дешёвая книга стоит больше 500
рублей. Выведите жанр и минимальную цену.
SELECT genre, MIN(price) AS min_price
FROM books
GROUP BY genre
HAVING MIN(price) > 500;
Задание 5: Комбинирование WHERE и HAVING
5.1. Среди книг, выпущенных после 1950 года, найдите авторов, у
которых средняя цена книги превышает 600 рублей. Выведите имя
автора и среднюю цену.
SELECT a.name, AVG(b.price) AS avg_price
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.publication_year > 1950
GROUP BY a.id, a.name
HAVING AVG(b.price) > 600;
5.2. Среди книг российских авторов (страна = 'Россия') найдите жанры, в
которых суммарное количество книг на складе превышает 30. Выведите
жанр и суммарное количество на складе.
SELECT b.genre, SUM(b.stock_quantity) AS total_stock
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.country = 'Россия'
GROUP BY b.genre
HAVING SUM(b.stock_quantity) > 30;
Задание 6: Использование COUNT(DISTINCT)
6.1. Посчитайте, сколько уникальных клиентов сделали заказы
(таблица orders).
SELECT COUNT(DISTINCT customer_name) AS unique_customers FROM
orders;
6.2. Для каждой книги посчитайте, сколько разных клиентов её
заказывали. Выведите название книги и количество уникальных
клиентов.
SELECT b.title, COUNT(DISTINCT o.customer_name) AS unique_customers
FROM books b
LEFT JOIN orders o ON b.id = o.book_id
GROUP BY b.id, b.title
ORDER BY unique_customers DESC;
Задание 7: Группировка по дате
7.1. Посчитайте количество заказов и общее количество проданных книг
для каждого месяца 2025 года. Выведите месяц (в формате ГГГГ-ММ),
количество заказов и общее проданное количество.
SELECT
 DATE_FORMAT(order_date, '%Y-%m') AS month,
 COUNT(*) AS order_count,
 SUM(quantity) AS total_books_sold
FROM orders
WHERE YEAR(order_date) = 2025
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
7.2. Для каждого месяца посчитайте выручку (цена книги × количество
в заказе). Выведите месяц и суммарную выручку, округлённую до двух
знаков.
SELECT
 DATE_FORMAT(o.order_date, '%Y-%m') AS month,
 ROUND(SUM(b.price * o.quantity), 2) AS total_revenue
FROM orders o
JOIN books b ON o.book_id = b.id
WHERE YEAR(o.order_date) = 2025
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month;
Задание 8: Творческое задание
8.1. Найдите самого популярного автора по количеству проданных
экземпляров книг. Учитывайте все заказы. Выведите имя автора и
общее количество проданных книг.
SELECT a.name, SUM(o.quantity) AS total_sold
FROM orders o
JOIN books b ON o.book_id = b.id
JOIN authors a ON b.author_id = a.id
GROUP BY a.id, a.name
ORDER BY total_sold DESC
LIMIT 1;
8.2. Найдите клиента, который потратил больше всего денег в магазине.
Выведите имя клиента и общую сумму его покупок.
SELECT o.customer_name, SUM(b.price * o.quantity) AS total_spent
FROM orders o
JOIN books b ON o.book_id = b.id
GROUP BY o.customer_name
ORDER BY total_spent DESC
LIMIT 1;
8.3. Определите, книги какого жанра принесли наибольшую выручку в
апреле 2025 года. Выведите жанр и сумму выручки.
SELECT b.genre, SUM(b.price * o.quantity) AS revenue
FROM orders o
JOIN books b ON o.book_id = b.id
WHERE o.order_date >= '2025-04-01' AND o.order_date < '2025-05-01'
GROUP BY b.genre
ORDER BY revenue DESC
LIMIT 1;
3. Задания.
Задание 1
1.1. Посчитайте общее количество клиентов в таблице clients.
1.2. Найдите самого опытного тренера (максимальное количество лет опыта).
1.3. Найдите наименьшую зарплату среди тренеров.
1.4. Посчитайте среднюю продолжительность тренировки в минутах.
Округлите до целого.
1.5. Посчитайте общую сумму, которую заплатили клиенты за все
тренировки.
Задание 2
2.1. Посчитайте количество клиентов по типам абонементов. Выведите тип
абонемента и количество клиентов.
2.2. Для каждого тренера найдите минимальную и максимальную стоимость
его тренировки. Выведите имя тренера, минимальную и максимальную цену.
2.3. Посчитайте общее количество минут, которое провёл с клиентами
каждый тренер. Выведите имя тренера и сумму минут. Отсортируйте по
убыванию суммы.
2.4. Найдите средний возраст клиентов для каждого пола. Выведите пол и
средний возраст (в годах, без дробной части).
Задание 3
3.1. Посчитайте количество тренировок в разрезе «специализация тренера —
пол клиента». Выведите специализацию, пол клиента и количество
тренировок.
3.2. Для каждого тренера и каждого типа абонемента клиента посчитайте
суммарную стоимость проведённых тренировок. Выведите имя тренера, тип
абонемента и сумму. Отсортируйте по тренеру, затем по убыванию суммы.
Задание 4
4.1. Найдите тренеров, у которых средняя стоимость тренировки превышает
1600 рублей. Выведите имя тренера и среднюю стоимость.
4.2. Найдите специализации, по которым работает более одного тренера. 
Выведите специализацию и количество тренеров.
4.3. Найдите клиентов, которые посетили более 2 тренировок. Выведите имя
клиента и количество тренировок.
4.4. Найдите типы абонементов, у которых средний возраст клиентов меньше
35 лет. Выведите тип абонемента и средний возраст.
Задание 5
5.1. Среди тренировок, проведённых в марте 2025 года, найдите тренеров, у
которых суммарная выручка превышает 4000 рублей. Выведите имя тренера
и сумму.
5.2. Среди клиентов женского пола найдите типы абонементов, по которым
средняя продолжительность тренировки больше 60 минут. Выведите тип
абонемента и среднюю продолжительность.
Задание 6
6.1. Посчитайте, сколько уникальных клиентов посетили тренировки
(таблица sessions).
6.2. Для каждого тренера посчитайте, сколько разных клиентов он
тренировал. Выведите имя тренера и количество уникальных клиентов.
Задание 7
7.1. Посчитайте количество тренировок и общую сумму выручки для
каждого месяца 2025 года. Выведите месяц (в формате ГГГГ-ММ),
количество тренировок и сумму выручки.
7.2. Для каждого месяца посчитайте среднюю продолжительность
тренировки. Выведите месяц и среднюю продолжительность, округлённую
до целого.
Задание 8
8.1. Найдите самого востребованного тренера по количеству проведённых
тренировок. Выведите имя тренера и количество тренировок.
8.2. Найдите клиента, который потратил больше всего денег на тренировки.
Выведите имя клиента и общую сумму.
8.3. Определите, тренировки по какой специализации принесли наибольшую
выручку в апреле 2025 года. Выведите специализацию и сумму выручки.







БД:
-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Хост: 127.0.0.1:3306
-- Время создания: Апр 20 2026 г., 09:19
-- Версия сервера: 10.8.4-MariaDB
-- Версия PHP: 8.1.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- База данных: `agregatnie_func_work`
--

-- --------------------------------------------------------

--
-- Структура таблицы `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` enum('М','Ж') COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `registration_date` date NOT NULL,
  `membership_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `clients`
--

INSERT INTO `clients` (`id`, `name`, `gender`, `birth_date`, `registration_date`, `membership_type`) VALUES
(1, 'Иван Петров', 'М', '1990-05-15', '2024-01-10', 'Годовой'),
(2, 'Мария Смирнова', 'Ж', '1985-08-22', '2024-02-15', 'Полугодовой'),
(3, 'Алексей Иванов', 'М', '1995-03-10', '2024-01-20', 'Месячный'),
(4, 'Елена Сидорова', 'Ж', '1988-11-30', '2024-03-01', 'Годовой'),
(5, 'Дмитрий Козлов', 'М', '1992-07-12', '2024-02-10', 'Полугодовой'),
(6, 'Анна Морозова', 'Ж', '1998-01-25', '2024-04-05', 'Месячный'),
(7, 'Сергей Волков', 'М', '1980-12-03', '2024-01-15', 'Годовой'),
(8, 'Ольга Новикова', 'Ж', '1993-09-18', '2024-03-20', 'Полугодовой'),
(9, 'Павел Жуков', 'М', '1987-04-08', '2024-02-01', 'Месячный'),
(10, 'Татьяна Романова', 'Ж', '1991-06-14', '2024-04-10', 'Годовой');

-- --------------------------------------------------------

--
-- Структура таблицы `sessions`
--

CREATE TABLE `sessions` (
  `id` int(11) NOT NULL,
  `trainer_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `session_date` date NOT NULL,
  `duration_minutes` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `sessions`
--

INSERT INTO `sessions` (`id`, `trainer_id`, `client_id`, `session_date`, `duration_minutes`, `price`) VALUES
(1, 1, 1, '2025-03-10', 60, '1500.00'),
(2, 1, 2, '2025-03-12', 90, '2000.00'),
(3, 1, 3, '2025-03-15', 60, '1500.00'),
(4, 2, 4, '2025-03-11', 60, '1400.00'),
(5, 2, 5, '2025-03-14', 90, '1900.00'),
(6, 3, 6, '2025-03-10', 45, '1200.00'),
(7, 3, 7, '2025-03-13', 60, '1600.00'),
(8, 4, 8, '2025-03-16', 60, '1300.00'),
(9, 5, 9, '2025-03-17', 90, '2200.00'),
(10, 6, 10, '2025-03-18', 60, '1500.00'),
(11, 1, 5, '2025-04-01', 60, '1500.00'),
(12, 2, 1, '2025-04-02', 90, '2000.00'),
(13, 3, 2, '2025-04-03', 45, '1200.00'),
(14, 4, 3, '2025-04-04', 60, '1300.00'),
(15, 5, 4, '2025-04-05', 90, '2200.00'),
(16, 6, 6, '2025-04-06', 60, '1500.00'),
(17, 1, 7, '2025-04-07', 60, '1500.00'),
(18, 2, 8, '2025-04-08', 90, '1900.00'),
(19, 3, 9, '2025-04-09', 60, '1600.00'),
(20, 4, 10, '2025-04-10', 60, '1300.00');

-- --------------------------------------------------------

--
-- Структура таблицы `trainers`
--

CREATE TABLE `trainers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `specialization` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `experience_years` int(11) NOT NULL,
  `salary` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `trainers`
--

INSERT INTO `trainers` (`id`, `name`, `specialization`, `experience_years`, `salary`) VALUES
(1, 'Иванов Алексей', 'Йога', 5, '45000.00'),
(2, 'Петрова Мария', 'Пилатес', 8, '52000.00'),
(3, 'Смирнов Денис', 'Кроссфит', 3, '40000.00'),
(4, 'Козлова Елена', 'Стретчинг', 10, '58000.00'),
(5, 'Васильев Олег', 'Бодибилдинг', 12, '65000.00'),
(6, 'Соколова Анна', 'Йога', 4, '43000.00');

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`);

--
-- Индексы таблицы `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `trainer_id` (`trainer_id`),
  ADD KEY `client_id` (`client_id`);

--
-- Индексы таблицы `trainers`
--
ALTER TABLE `trainers`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `clients`
--
ALTER TABLE `clients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT для таблицы `sessions`
--
ALTER TABLE `sessions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- AUTO_INCREMENT для таблицы `trainers`
--
ALTER TABLE `trainers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Ограничения внешнего ключа сохраненных таблиц
--

--
-- Ограничения внешнего ключа таблицы `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`trainer_id`) REFERENCES `trainers` (`id`),
  ADD CONSTRAINT `sessions_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;