Вычисления и подведение итогов в запросах

Предложение GROUP BY

Часто в запросах требуется формировать промежуточные итоги, что обычно отображается появлением в запросе фразы "для каждого...". Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

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

Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.

Стандартом SQL определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.

Пример 6.9. Вычислить средний объем покупок, совершенных каждым покупателем.

SELECT Клиент.Фамилия, Avg(Сделка.Количество) 
    AS Среднее_количество
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фамилия

Фраза "каждым покупателем" нашла свое отражение в SQL-запросе в виде предложения GROUP BY Клиент.Фамилия.

Пример 6.10. Определить, на какую сумму был продан товар каждого наименования.

SELECT Товар.Название, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Стоимость
FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название

Пример 6.11. Подсчитать количество сделок, осуществленных каждой фирмой.

SELECT Клиент.Фирма, Count(Сделка.КодСделки) 
    AS Количество_сделок
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма

Пример 6.12. Подсчитать общее количество купленного для каждой фирмы товара и его стоимость.

SELECT Клиент.Фирма, Sum(Сделка.Количество) 
    AS Общее_Количество, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Стоимость
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента)
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма

Пример 6.13. Определить суммарную стоимость каждого товара за каждый месяц.

SELECT Товар.Название, Month(Сделка.Дата) 
    AS Месяц,
    Sum(Товар.Цена*Сделка.Количество) 
    AS Стоимость
FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название, Month(Сделка.Дата)

Пример 6.14. Определить суммарную стоимость каждого товара первого сорта за каждый месяц.

SELECT Товар.Название, Month(Сделка.Дата) 
    AS Месяц,
    Sum(Товар.Цена*Сделка.Количество) 
    AS Стоимость
FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Сорт="Первый"
GROUP BY Товар.Название, Month(Сделка.Дата)

Предложение HAVING

При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор.

Условия в HAVING отличаются от условий в WHERE:

• HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;
• WHERE исключает из расчета агрегатных значений по группировке записи, не удовлетворяющие условию;
• в условии поиска WHERE нельзя задавать агрегатные функции.

Пример 6.15. Определить фирмы, у которых общее количество сделок превысило три.

SELECT Клиент.Фирма, Count(Сделка.Количество)
    AS Количество_сделок
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма
HAVING Count(Сделка.Количество)>3

Пример 6.16. Вывести список товаров, проданных на сумму более 10000 руб.

SELECT Товар.Название, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Стоимость
FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000

Пример 6.17. Вывести список товаров, проданных на сумму более 10000 без указания суммы.

SELECT Товар.Название
FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000