Групповые функции и предложение GROUP BY

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

Пример 62

Задача.

Вывести наибольшую, наименьшую и среднюю оценку для каждого студента.

Решение.

SELECT P.NRecordBook [№ зачетки],

STname [Имя студента],

MAX(Mark) [Максимальная оценка],

MIN(Mark) [Минимальная оценка],

AVG(Mark)[Средняя оценка]

FROM Progress P INNER JOIN Student S

ON P.NRecordBook=S.NRecordBook

GROUP BY P.NRecordBook, StName

Результат реализации запроса:

Для каждой группы будет выведена только одна строка.

С предложением GROUP BY могут быть использованы все остальные команды SELECT, например, с помощью предложения WHERE можно исключить строки, которые не должны участвовать в запросе, допустим строки с признаком NULL. Рассмотрим это на примере применения агрегированной функции COUNT(). Функция COUNT() с аргументом отличным от *, игнорирует строки с признаком NULL, в то время как функция вида COUNT(*) посчитает все строки, в том числе и строки, в которых есть атрибуты с признаком NULL.

SELECT COUNT(*),

SUM(Mark),

AVG(Mark)

FROM Progress

Результат реализации запроса:

В следующем случае функция COUNT() посчитает строки, в которых оценка NOT NULL, и функции SUM() и AVG() будут учитывать только те строки, в которых оценка NOT NULL.

SELECT COUNT(Mark),

SUM(Mark),

AVG(Mark)

FROM Progress

Результат реализации запроса:

Здесь функция COUNT() будет работать с теми же строками, что и функции SUM() и AVG(). Исключить возможные неоднозначности можно, включив в запрос условие отбора строк: предложение WHERE mark IS Not NULL.

SELECT COUNT(*),

SUM(Mark),

AVG(Mark)

FROM Progress

WHERE Mark IS NOT NULL

Результат реализации запроса:

Однако использование в предложении WHERE агрегированной функции приводит к ошибке.

Пример 63

Задача.

Вывести среднюю оценку по каждой студенческой группе, если средняя оценка выше 3.

Решение.

SELECT NameGroup, AVG(Mark)[Средняя оценка]

FROM Progress P INNER JOIN Student S

ON P.NRecordBook=S.NRecordBook

INNER JOIN SGroup SG

ON S.IDGroup=SG.IDGroup

WHERE AVG(Mark)>3

GROUP BY NameGroup

Результат реализации запроса:

Server: Msg 147

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Пример 64

Задача.

Вывести среднюю оценку по каждой студенческой группе, если средняя оценка выше 3. Значение оценки округлить до одной десятой.

Решение.

SELECT NameGroup, Round(AVG(Mark),1)[Средняя оценка]

FROM Progress P INNER JOIN Student S

ON P.NRecordBook=S.NRecordBook

INNER JOIN SGroup SG

ON S.IDGroup=SG.IDGroup

GROUP BY NameGroup

HAVING AVG(Mark)>3

Результат реализации запроса: