GROUP BY <группирующий-элемент> [COLLATE последовательность-сортировки]

[, <группирующий-элемент> [. . ] ]

HAVING <предикат-группируемых-столбцов>

[ORDER BY .. .] ;

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

* Столбец базы данных или неагрегатное выражение не могут быть указаны в списке столбцов, если они не указаны в предложении GROUP BY.

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

! ! !

ВНИМАНИЕ! В Firebird 1.0.x и InterBase требования к допустимости группирования являются менее ограничивающими. Группирующие запросы и клиентские вызовы хранимых процедур могут вызывать исключения, которые ранее не появлялись. Это давняя ошибка, о которой вы должны знать при миграции ваших старых приложений в Firebird версии 1.5 и выше.

. ! .

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

Агрегатными функциями являются SUMO, которая вычисляет итоги, MINO и MAXO, отыскивающие наименьшее и наибольшее значение соответственно, и AVGO, вычисляющая среднее значение. Функция COUNT также ведет себя как агрегатная функция в группирующих запросах, возвращая счетчик строк для всех строк ниже контекста (уровня)группы.

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

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

SELECT

PROJ_ID,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY PROJ_ID;

Эти две спецификации поля хороши в качестве группируемых элементов. Идентификатор отдела (DEPT_NO) не присутствует в списке, потому что список задает нужные нам по проекту итоги. Для получения этих итогов мы используем аргумент PROJ_ID В предложении GROUP BY.

С другой стороны, если мы хотим получить список бюджетов отделов, независимых от проектов, список полей должен включать DEPT_NO, чтобы он был аргументом в предложении GROUP BY:

SELECT

DEPT_NO,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY DEPT_NO;

В агрегатных выражениях типа SUMO, AVG и COUNT(<имя-столбца>) строки, содержащие NULL в соответствующем столбце, игнорируются. Функция AVG создает числитель, суммируя все непустые значения, и знаменатель, подсчитывая строки, содержащие непустые значения.

! ! !

ПРИМЕЧАНИЕ. Если у вас есть столбцы, по которым вы собираетесь вычислять среднее значение, важно решить в процессе проектирования, как при вычислении среднего значения вы будете трактовать "пустые" экземпляры - как NULL (они будут исключены из вычисления) или как ноль. Вы можете реализовать нужное правило, используя значение по умолчанию или (лучше) триггер BEFORE INSERT [86] .

. ! .

Предложение GROUP BY получает список группирующих элементов:

* в Firebird 1.0.x группирующим элементом может быть только имя столбца или соответствующее выражение внешней функции (UDF);

* Firebird 1.5 расширил диапазон группирующих элементов, добавив также номер, порядковое числительное, представляющее номер позиции (слева направо) соответствующего элемента из списка полей SELECT. Этот номер может быть использован вместе с существующей возможностью для аргументов ORDER BY;