GROUP BY. Группировка колонок в MySQL

Команда 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)