Использование GROUP BY по нескольким столбцам

Group By X означает собрать в одну группу все записи с одинаковым значением X.

Group By X, Y означает собрать в одну группу все записи с одинаковыми значениями как для X, так и для Y.

Для иллюстрации рассмотрим следующий пример таблицы, связанной с тем, кто посещает какие предметы в университете:

Таблица: Subject_Selection

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

Когда вы выполняете group by только по столбцу subject, например:

select Subject, Count(*)
from Subject_Selection
group by Subject

Вы получите что-то вроде:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

...поскольку для ITB001 5 записей, а для MKB114 — 2.

Если мы сделаем group by по двум столбцам:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

Мы получим:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

Это происходит потому, что, когда мы группируем по двум столбцам, это означает "Соберите так, чтобы все записи с одинаковыми значениями предмета и семестра находились в одной группе, а затем вычислите все агрегатные функции (Count, Sum, Average и т.д.) для каждой из этих групп". В этом примере это демонстрируется тем, что, когда мы считаем, мы видим, что трое человек учатся на ITB001 в семестре 1, и два — в семестре 2. Оба человека, проходящие MKB114, учатся в семестре 1, поэтому нет строки для семестра 2 (нет данных, подходящих к группе "MKB114, семестр 2").

Надеюсь, это понятным образом объясняет.

В данном ответе я объясню не только использование оператора GROUP BY, но и применение агрегатных функций.

Оператор GROUP BY используется в сочетании с агрегатными функциями для группировки результирующего набора по одному или нескольким столбцам. Например:

-- GROUP BY с одним параметром:
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

-- GROUP BY с двумя параметрами:
SELECT
    column_name1,
    column_name2,
    AGGREGATE_FUNCTION(column_name3)
FROM
    table_name
GROUP BY
    column_name1,
    column_name2;

Запомните этот порядок:

1. SELECT (используется для выбора данных из базы данных)
2. FROM (предназначен для перечисления таблиц)
3. WHERE (для фильтрации записей)
4. GROUP BY (можно использовать в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам)
5. HAVING (используется в сочетании с оператором GROUP BY для ограничения групп возвращаемых строк только теми, у которых условие TRUE)
6. ORDER BY (ключевое слово для сортировки результирующего набора)

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

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

• MIN() - возвращает наименьшее значение в заданном столбце
• MAX() - возвращает максимальное значение в заданном столбце
• SUM() - возвращает сумму числовых значений в заданном столбце
• AVG() - возвращает среднее значение заданного столбца
• COUNT() - возвращает общее количество значений в заданном столбце
• COUNT(*) - возвращает количество строк в таблице

Примеры SQL-скриптов с использованием агрегатных функций:

Допустим, нам нужно найти заказы на продажу, общая сумма которых превышает $950. Мы объединяем оператор HAVING и GROUP BY, чтобы достичь этой цели:

SELECT 
    orderId, SUM(unitPrice * qty) AS Total
FROM
    OrderDetails
GROUP BY orderId
HAVING Total > 950;

Подсчитываем все заказы, группируя их по customerId и сортируя результат по возрастанию. Мы используем функцию COUNT вместе с операторами GROUP BY и ORDER BY:

SELECT 
    customerId, COUNT(*)
FROM
    Orders
GROUP BY customerId
ORDER BY COUNT(*) ASC;

Извлекаем категории, у которых средняя цена за единицу превышает $10, используя функцию AVG, комбинированную с операторами GROUP BY и HAVING:

SELECT 
    categoryName, AVG(unitPrice) AS AveragePrice
FROM
    Products p
INNER JOIN
    Categories c ON c.categoryId = p.categoryId
GROUP BY categoryName
HAVING AVG(unitPrice) > 10;

Находим самый дешевый продукт в каждой категории, используя функцию MIN в подзапросе:

SELECT categoryId,
       productId,
       productName,
       unitPrice
FROM Products p1
WHERE unitPrice = (
                SELECT MIN(unitPrice)
                FROM Products p2
                WHERE p2.categoryId = p1.categoryId);

Теперь рассмотрим, как выбрать самую последнюю дату productDate, используя функцию MAX в подзапросе:

SELECT categoryId,
       productId,
       productName,
       unitPrice,
       productDate
FROM Products p1
WHERE productDate = (
                  SELECT MAX(productDate) 
                  FROM Products p2
                  WHERE p2.categoryId = p1.categoryId);

Следующий запрос группирует строки с одинаковыми значениями в столбцах categoryId и productId:

SELECT 
    categoryId, categoryName, productId, SUM(unitPrice) AS TotalPrice
FROM
    Products p
INNER JOIN
    Categories c ON c.categoryId = p.categoryId
GROUP BY categoryId, productId;

Если задать вопрос: - что будет, если использовать GROUP BY, но не применять агрегатную функцию? Ответ: Мы также можем использовать GROUP BY без применения агрегатной функции. Вот пример, где мы группируем по categoryId:

SELECT categoryId,
       productId,
       productName,
       unitPrice
FROM Products
GROUP BY categoryId;

Когда вы используете оператор GROUP BY x, он извлекает строки для каждого уникального значения в столбце x.

Когда вы используете оператор GROUP BY x, y, он извлекает строки для каждой уникальной пары значений столбцов x и y. Добавляя больше столбцов в предложение GROUP BY, вы создаете более детализированные и специфические подмножества данных в результирующем наборе; следовательно, вы достигаете большей гранулярности.