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

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

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

Синтаксис предложения GROUP BY имеет следующий вид:

GROUP BY <элемент группировки1> [, <элемент группировки2>] ,

<элемент группировки> := {[<таблица>.] столбец

порядковый номер столбца псевдоним столбца <выражение>}.

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

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

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

SELECT NachislYear, AVG(NachislSum) FROM NachislSumma GROUP BY NachislYear;.

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

Рис. 3.54. Результат выполнения запроса с группировкой

Возможна группировка результатов запроса на основании порядкового номера возвращаемого элемента в предложении SELECT. Таким образом, запрос

SELECT NachislYear, AVG(NachislSum) FROM NachislSumma

GROUP BY 1;

выдаст такой же результат, как и предыдущий запрос.

Например, если для каждого абонента требуется вывести общее количество оплат с указанным в этой же строке максимальным для него значением оплаты, то можно сгруппировать результат по номеру лицевого счета и использовать в качестве второго возвращаемого элемента выражение с агрегатными функциями COUNT и MAX. Соответствующий запрос будет выглядеть следующим образом:

SELECT AccountCD, (COUNT(*) - с максимальной суммой

MAX(PaySum)) AS Pay Count

FROM PaySumma

GROUP BY 1;.

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

Рис. 3.55. Результат выполнения запроса с группировкой

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

SELECT NachislSum AS Summa 50, COUNT(*) FROM NachislSumma WHERE NachislSum > 50

GROUP BY Summa 50;.

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

Рис. 3.56. Результат выполнения запроса с группировкой по псевдониму

В SQL можно группировать результаты запроса на основании двух или более элементов.

Например, необходимо для каждого абонента вывести наименьшее значение оплаты за услуги газоснабжения за 1999 и 2000 годы. Для этого нужно сгруппировать таблицу PaySumma по номерам лицевых счетов абонентов и по годам, применяя к значениям оплат агрегатную функцию MIN следующим образом:

SELECT AccountCD, PayYear, MIN(Paysum) FROM PaySumma WHERE PayYear IN (1999, 2000) GROUP BY AccountCD, PayYear;.

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

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

СУБД Firebird в качестве элемента группировки допускает определение выражений, в которых разрешено использовать SQL-функции SUBSTRING и EXTRACT, а также операцию CASE и функции вывода.