Команда GROUP BY
GROUP BY чаще всего используется совместно с собирательными функциями (COUNT(), MIN(), MAX(), SUM(), AVG()) для группировки результатов в одной или нескольких колонках
Синтаксис GROUP BY
SELECT column1, column2, ... , columnN FROM table_name
WHERE condition GROUP BY column1, column2, ... , columnN
Пример группировки колонок с помощью GROUP BY
Далее попробуем сгруппировать несколько колонок таблицы books из базы данных книжного магазина Bookstore.
Выведем, для ознакомления, все записи таблицы books.
mysql> SELECT id, title, author, price, amount FROM books;
+----+---------------------------+--------------------+--------+--------+
| id | title | author | price | amount |
+----+---------------------------+--------------------+--------+--------+
| 1 | Дубровский | Александр Пушкин | 230.00 | 20 |
| 2 | Нос | Николай Гоголь | 255.20 | 7 |
| 3 | Мастер и Маргарита | Михаил Булгаков | 263.00 | 8 |
| 4 | Мёртвые души | Николай Гоголь | 230.00 | 3 |
| 5 | Преступление и наказание | Фёдор Достоевский | 230.00 | 3 |
| 6 | Война и мир | Лев Толстой | 346.00 | 1 |
| 7 | Анна Каренина | Лев Толстой | 346.00 | 0 |
| 8 | Отцы и дети | Иван Тургенев | 371.00 | 3 |
| 9 | Собачье сердце | Михаил Булгаков | 232.00 | 0 |
| 10 | Бесы | Фёдор Достоевский | 212.00 | 8 |
+----+---------------------------+--------------------+--------+--------+
10 rows in set (0.00 sec)
Допустим нам необходимо посчитать количество книг на складе, чтобы узнать, сколько книг предстоит заказать у поставщиков в ближайшее время.
Воспользуемся функцией подсчета записей - COUNT() и сгруппируем результат по колонке amount (количество).
mysql> SELECT id, title, author, price, amount, COUNT(id) FROM books
-> GROUP BY amount;
+----+---------------------+-------------------+--------+--------+-----------+
| id | title | author | price | amount | count(id) |
+----+---------------------+-------------------+--------+--------+-----------+
| 7 | Анна Каренина | Лев Толстой | 346.00 | 0 | 2 |
| 6 | Война и мир | Лев Толстой | 346.00 | 1 | 1 |
| 4 | Мёртвые души | Николай Гоголь | 230.00 | 3 | 3 |
| 2 | Нос | Николай Гоголь | 255.20 | 7 | 1 |
| 3 | Мастер и Маргарита | Михаил Булгаков | 263.00 | 8 | 2 |
| 1 | Дубровский | Александр Пушкин | 230.00 | 20 | 1 |
+----+---------------------+-------------------+--------+--------+-----------+
6 rows in set (0.00 sec)
В результате мы узнаем, что двух книг у нас не осталось совсем, 1 книга только в одном экземпляре, 3 книги по 3 экземпляра и т.д.
Результаты колонок id, title, author, price в данном случаем не имеют никакого смысла, поэтому в следующих примерах мы будем выводить результаты только тех колонок по которым будем производить группировку.
Теперь посчитаем сколько есть книг, имеющие одного и того же автора и при этом одинаковую цену. В данном случае будем группировать по двум колонкам: author и price. Лишние колонки (id, author и amount) выводить не будем.
mysql> SELECT author, price, COUNT(id) AS 'Число книг'
-> FROM books
-> GROUP BY author, price;
+--------------------+--------+------------+
| author | price | Число книг |
+--------------------+--------+------------+
| Александр Пушкин | 230.00 | 1 |
| Иван Тургенев | 371.00 | 1 |
| Лев Толстой | 346.00 | 2 |
| Михаил Булгаков | 232.00 | 1 |
| Михаил Булгаков | 263.00 | 1 |
| Николай Гоголь | 230.00 | 1 |
| Николай Гоголь | 255.20 | 1 |
| Фёдор Достоевский | 212.00 | 1 |
| Фёдор Достоевский | 230.00 | 1 |
+--------------------+--------+------------+
9 rows in set (0.00 sec)
В данном примере можно заметить, что у нас есть две книги за авторством Льва Толстого и при этом имеющие одну и туже цену 346.00.
Группировка в таблицах, объединенных с помощью JOIN.
Предположим нам необходимо узнать кто из покупателей книжного интернет-магазина - Bookstore сделал больше всего заказов. Для этого возьмем данные из связанных таблиц: customers (клиенты) и orders (заказы).
Выведем содержимое таблиц customers и orders для ознакомления.
mysql> SELECT id, first_name, last_name
-> FROM customers;
+----+----------------+----------------+
| id | first_name | last_name |
+----+----------------+----------------+
| 1 | Олег | Пальшин |
| 2 | Jane | Doherty |
| 3 | Евгений | Серов |
| 4 | София | Молина |
| 5 | John | Doe |
+----+----------------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT id, customer_id, status, order_date FROM orders;
+----+-------------+------------------+---------------------+
| id | customer_id | status | order_date |
+----+-------------+------------------+---------------------+
| 3 | 1 | Готов к отправке | 2019-01-05 04:55:58 |
| 6 | 3 | Готов к отправке | 2019-01-15 14:56:12 |
| 7 | 5 | Завершен | 2019-01-11 20:59:40 |
| 9 | 3 | Обрабатывается | 2018-12-22 21:16:16 |
| 10 | 5 | Готов к отправке | 2018-12-24 04:28:54 |
| 14 | 1 | Ждет оплаты | 2018-12-30 15:42:18 |
| 15 | 5 | Новый | 2018-12-29 02:11:21 |
| 16 | 4 | Готов к отправке | 2018-12-31 20:56:25 |
| 20 | 4 | Ждет оплаты | 2019-01-19 11:14:33 |
| 21 | 1 | Завершен | 2019-01-14 02:30:04 |
+----+-------------+------------------+---------------------+
10 rows in set (0.00 sec)
Объединим обе эти таблицы с помощью команды LEFT JOIN и посчитаем сколько заказов сделал каждый из клиентов.
mysql> SELECT customers.id, customers.first_name,
-> customers.last_name, COUNT(orders.id) AS 'Число заказов'
-> FROM customers
-> LEFT JOIN orders ON orders.customer_id = customers.id
-> GROUP BY customers.id;
+----+----------------+----------------+----------------+
| id | first_name | last_name | Число заказов |
+----+----------------+----------------+----------------+
| 1 | Олег | Пальшин | 3 |
| 2 | Jane | Doherty | 0 |
| 3 | Евгений | Серов | 2 |
| 4 | София | Молина | 2 |
| 5 | John | Doe | 3 |
+----+----------------+----------------+----------------+
5 rows in set (0.00 sec)