Предложение group by (применение агрегатных функций)

Задает группы, в которые должны быть помещены строки вывода. При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.

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

При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы.

Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

Синтаксис

GROUP BY [ ALL ] group_by_expression [ ,...n ]

Аргументы

ALL - Включает все группы и результирующие наборы (даже не имеющие строк), которые удовлетворяют условию поиска заданного в предложении WHERE.

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

group_by_expression - выражение, по которому выполняется группирование. Аргумент group_by_expression также называется столбцом группирования.

Аргумент group_by expression может быть столбцом или нестатистическим выражением, которое ссылается на столбец, возвращаемый предложением FROM.

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

Столбцы типа text, ntext и image нельзя использовать в аргументе group_by_expression.

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

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

Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице.

Агрегатные функции
Функция	Результат
COUNT	Количество строк или непустых значений полей, которые выбрал запрос
SUM	Сумма всех выбранных значений данного поля
AVG	Среднеарифметическое значение всех выбранных значений данного поля
MIN	Наименьшее из всех выбранных значений данного поля
MAX	Наибольшее из всех выбранных значений данного поля

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент.

С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля.

При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке.

Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.

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

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

Пример:

SELECT R1.Дисциплина, COUNT(*)

FROM R1

GROUP BY R1.Дисциплина

Пример:

SELECT DATEPART(yyyy,OrderDate) AS Year,

SUM(TotalDue) AS AverageOrderAmt

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

SUM(TotalDue) – имя столбца TotalDue используется с агрегатной функцией SUM (отсутствует в GROUP BY)

Если в запрос включена группировка, все стадии, следующие за стадией group by, включая having, select и order BY, должны оперировать группами, а не отдельными строкам.

В итоге в окончательном результате запроса каждая группа представлена одной строкой. Это означает, что все выражения, которые задаются в элементах инструкции, обрабатываемых на стадиях, следующих за стадией group by, должны возвращать скалярную величину (одно значение) для каждой группы.

Выражения на основе элементов, входящих в список условия group by, удовлетворяют этому требованию, т. к. по определению у каждой группы есть только один уникальный вариант всех элементов условия group by.

Пример: запрос и результат

SELECT empid, YEAR(orderdate) AS orderyear

FROM Sales.Orders

WHERE custid =71 order by empid

Результат 31строка, из которых 16 уникальных комбинаций empid и orderyear.

Если выполнить запрос:

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*)

FROM Sales.Orders

WHERE custid =71

GROUP BY empid, YEAR(orderdate);

Получим группы этих комбинаций:

Поскольку группирующая функция возвращает одно значение на группу, элементы, не включенные в список условия GROUP by, разрешены только как входные параметры для статистических или агрегатных функций, таких как count, sum, avg, min или min.

Напри­мер, запрос

SELECT empid, YEAR(orderdate) AS orderyear,

SUM(freight) AS totalfreight,

COUNT(*) AS numorders

FROM Sales.Orders

WHERE custid =71 GROUP BY empid, YEAR(orderdate);

вернет результат:

При удалении из запроса одной агрегатной функции

SELECT

empid,

YEAR(orderdate) AS orderyear,

freight,

COUNT(*) AS numorders

FROM Sales.Orders

WHERE custid =71 GROUP BY empid, YEAR(orderdate);

выдается ошибка:

Msg 8120, Level 16, State 1, Line 1

Column 'Sales.Orders.freight' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

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

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

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

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

Пример:

SELECT R1.Дисциплина, COUNT(*)

FROM R1

GROUP BY R1.Дисциплина

Результат:

Дисциплина	COUNT(*)
Базы данных	6
Теория информации	4
Сети и телекоммуникации	3
Английский язык	4

Можно сосчитать количество сдавших экзамен по какой-либо дисциплине, исключив неопределенные значения из исходного отношения перед группировкой. В этом случае запрос будет выглядеть следующим образом:

SELECT R1.Дисциплина, COUNT(*)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY R1.Дисциплина

Результат:

Дисциплина	COUNT(*)
Базы данных	6
Теория информации	3
Сети и телекоммуникации	3
Английский язык	4

Примечание: ALL - Включает все группы и результирующие наборы (даже не имеющие строк), которые удовлетворяют условию поиска заданного в предложении WHERE.

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

Пример:

USE AdventureWorks ;

GO

SELECT ProductID, AVG(UnitPrice) AS 'Average Price'

FROM Sales.SalesOrderDetail

WHERE OrderQty > 10

GROUP BY ProductID

ORDER BY ProductID ;

GO

Результат:

USE AdventureWorks ;

GO

SELECT ProductID, AVG(UnitPrice) AS 'Average Price'

FROM Sales.SalesOrderDetail

WHERE OrderQty > 10

GROUP BY ALL ProductID

ORDER BY ProductID ;

GO

Пример. Вычислить средний объем покупок (функция Avg), совершенных каждым покупателем (GROUP BY Клиент.Фамилия).

SELECT Клиент.Фамилия, Avg(Сделка.Количество)

AS Среднее_количество

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента

GROUP BY Клиент.Фамилия

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

SELECT Товар.Название,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название

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

SELECT Клиент.Фирма, Count(Сделка.КодСделки)

AS Количество_сделок

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента

GROUP BY Клиент.Фирма

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

SELECT Клиент.Фирма, Sum(Сделка.Количество)

AS Общее_Количество,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Клиент.Фирма

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

SELECT Товар.Название, Month(Сделка.Дата) AS Месяц,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название, Month(Сделка.Дата)

Примечание: Month(Сделка.Дата) -возвращает целое число, представляющее месяц указанной даты.

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

Пример (количество успешно сданных экзаменов):

SELECT COUNT(*)

FROM R1

WHERE Оценка > 2;

Для того, чтобы вычислить, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца.

Пример (количество различных оценок, полученных по каждой дисциплине):

SELECT R1.Дисциплина, COUNT(DISTINCT R1.Оценка)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY R1.Дисциплина

Результат:

Дисциплина	COUNT(DISTINCT R1.Оценка)
Базы данных	3
Теория информации	3
Сети и телекоммуникации	2
Английский язык	3

Сравните с запросом и результатом:

SELECT R1.Дисциплина, COUNT(*)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY R1.Дисциплина

Результат:

Дисциплина	COUNT(*)
Базы данных	6
Теория информации	3
Сети и телекоммуникации	3
Английский язык	4

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

Запрос без GROUP BY

SELECT R2.Оценка, R2.Дисциплина

FROM R1, R2

WHERE R1.ФИО = R2.ФИО

Результат:

Пример:

SELECT R2.Оценка, R2.Дисциплина, COUNT(*)As Qnt, Avg(Оценка) AS Ball

FROM R1, R2

WHERE R1.ФИО = R2.ФИО AND R2.Оценка IS NOT NULL

AND R2.Оценка >2

GROUP BY R2.Оценка, R2.Дисциплина

Результат: