11.3. Использование группировок записей (group by).

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

GROUP BY столбец [,столбец1 ...]

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

ПРИМЕР

Определите количество книг, хранящихся в различных фондах, а также их суммарную стоимость.

SELECT FundCode, COUNT (BookCode) AS count, SUM(Cost) AS sum

FROM BookInventaryNumbers

GROUP BY FundCode

ORDER BY FundCode

Нет необходимости включать имена столбцов BookCode и Cost в список фразы GROUP BY, поскольку в списке предложения SELECT они используются только в обобщающих функциях. В то же время, столбец FundCode в списке предложения SELECT не связан ни с какой обобщающей функцией и по этой причине обязательно должен быть указан в фразе GROUP BY.

Результат выполнения запроса приведен в таблице 11.7.

Таблица 11.7

FundCode	Count	Sum
1	4	84.89
2	2	66.88

Концептуально, при обработке этого запроса выполняются следующие действия.

1. Строки таблицы BookInventaryNumbers распределяются в группы в соответствии со значениями в столбце номера кода фонда (FundCode). В пределах каждой из групп оказываются данные о кодах книг одного из фондов. В нашем примере будут созданы две группы.

FundCode	BookCode	Cost			СOUNT(BookCode)	SUM(Cost)
1	1	15.56			4	84.89
1	2	22.33
1	3	34.01
1	4	12.99
2	5	56.78			2	66.88
2	6	10.10

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

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

11.4. Предложение having – наложение ограничений на группировку записей.

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

HAVING < условия_поиска >

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

Стандарт ISO требует, чтобы имена столбцов, используемые во фразе HAVING, обязательно присутствовали в списке фразы GROUP BY или применялись в обобщающих функциях. На практике условия поиска во фразе HAVING всегда включают, по меньшей мере, одну обобщающую функцию, в противном случае эти условия поиска должны быть помещены в предложение WHERE и применяться для отбора отдельных строк.

ПРИМЕР

Для каждого книжного фонда, где хранится более двух книг, определите количество книг, а также их суммарную стоимость.

SELECT FundCode, COUNT (BookCode) AS Count, SUM(Cost) AS Sum

FROM BookInventaryNumbers

GROUP BY FundCode

HAVING COUNT (BookCode) > 2

ORDER BY FundCode

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

Результат выполнения запроса приведен в таблице 11.8.

Таблица 11.8

FundCode	Count	Sum
1	4	84.89

ЗАМЕЧАНИЕ. Следует всегда помнить, чем условие в HAVING отличается от условия в WHERE:

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

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

• в условии поиска WHERE нельзя указывать агрегатные функции.