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

· MAX (поле) – максимальное значение поля;

· MIN (поле) – минимальное значение поля;

· AVG (поле) – среднее значение поля;

· SUM (поле) – сумма значений поля;

· COUNT (поле) – количество разных значений поля.

Трудно представить запрос типа “подсчитать, сколько номеров заказов”, а не ”сколько заказов”, поэтому в SQL используется специальная агрегатная функция COUNT(*), которая подсчитывает строки, а не значения данных. Именно эта форма функции COUNT обычно применяется на практике. Вместе с тем форма COUNT (поле) позволяет найти количество строк, которые имеют в указанном поле значения, отличные от NULL.

Пример итогового запроса

SELECT SUM(Plata) AS Summa, AVG (Dohod) AS Mid

FROM A WHERE Firma = ‘Махаон’

Из таблицы A выбираются только записи со значением ‘Махаон’ поля Firma и для них определяются суммарное значение поля Plata, а также среднее значение поля Dohod. Результирующая таблица содержит единственную запись с наименованиями полей Summa и Mid.

При вычислении агрегатных функций не учитываются те записи, у которых соответствующее поле имеет значение NULL. Это может приводить к неожиданным эффектам. Например, выражения SUM(Sale)–SUM(Tax) и SUM(Sale-Tax) могут иметь разную величину, если поля Sale и Tax принимают NULL-значения в разных записях неодновременно.

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

SELECT <список выражений> FROM < список таблиц>

GROUP BY <список полей группировки> HAVING <условие>

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

Пусть, например, имеется таблица денежных выплат сотрудникам A (Nom, Name, Money), в которой имена однозначно идентифицируют сотрудника, но каждый сотрудник может иметь несколько выплат. Требуется получить итоговую ведомость для тех сотрудников, которые имеют общую сумму выплат более 1000. Это можно сделать запросом

SELECT Name, SUM(Money) AS Itog FROM A

GROUP BY Name HAVING SUM(Money)>1000

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

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

Пусть, например, имеется таблица A (Facult, Gr, Name, Subj, Mark), где Facult - факультет, Gr – наименование группы, Name – фамилия студента, Subj – предмет, Mark – полученная оценка. Будем считать, что в пределах группы нет однофамильцев, а в пределах факультета наименования групп уникальны. Запрос

SELECT Facult, Gr, AVG (Mark) AS Mid FROM A

WHERE Subj <> ‘Физкультура’

GROUP BY Facult, Gr HAVING COUNT(*)>10

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

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

Дата добавления: 2014-01-07