Итоговые запросы позволяют производить итоговые вычисления по группам данных. Создание итогового запроса происходит в 2 этапа:
1. Группирование данных по значениям определённого поля.
§ Выбрать команду Вид Групповая операция или в режиме Конструктора запроса нажать кнопку Групповые операции на панели инструментов.
§ Обратить внимание на появление строки «Групповая операция» в бланке запроса. Команда Групповая операция дает установку Группировка для любого поля, занесённого в бланк запроса. В этом случае записи таблицы группируются, но итоги не подводятся.
G Примечание. Поле, значения которого нужно группировать, желательно поставить первым в списке выводимых полей.
2. Вычисление итогов в группе.
Заменить в соответствующем поле установку Группировка в строке Групповая операция на конкретные итоговые функции. Задать нужную функцию можно, указав её в раскрывающемся списке Группировка.
G Примечания.
1) MS Access предоставляет 9 статистических функций SQL, обеспечивающих выполнение групповых операций (табл.1).
|
|
2) В раскрывающемся списке строки Групповая операция также имеется установка Выражение. Выбрав её, можно задать в строке Поле вычисляемое выражение, в котором используется одна или несколько итоговых функций.
3) Строка Групповая операция в каждом поле должна иметь определённое значение. Если нет необходимости производить какие-либо групповые операции над полем, не рекомендуется включать его в запрос.
4) Группировка в двух и более полях означает вложенное группирование, т.е. создание группы внутри группы, заданной в предыдущем столбце (см. ниже пример 4).
Таблица 1. Статистические функции SQL.
Функция SQL | Действие |
Sum | Суммирование значений определенного поля |
Avg | Вычисление среднего значения данных определенного поля |
Min | Вычисление минимального значения поля |
Max | Вычисление максимального значения поля |
Count | Вычислениеколичества записей, отобранных запросом по условию |
StDev | Вычисляется стандартное отклонение значений данного поля для всех записей, отобранных запросом |
First | Определяется первоезначение в указанном поле записей, отобранных запросом |
Last | Определяется последнее значение в указанном поле записей, отобранных запросом |
Var | Вычисляетсявариация[2] значений данного поля для всех записей, отобранных запросом |
Примеры итоговых запросов:
1. Вычислить, сколько и на какую сумму продано товаров каждым продавцом.
Выполнение:
- Ввести в запросный бланк поля: ФИО (табл. Продавец), Количество (табл. Продажи), Цена (табл. Товар);
- Создать вычисляемое поле Стоимость с помощью Построителя выражений (формула приведена на рис. 5);
|
|
- сформировать группы по полю ФИО из таблицы Продавец ( - Групповая операция);
- в поле Количество и Стоимость ввести функцию Sum;
- сохранить запрос под именем «Итоги».
Рис.5. Бланк итогового запроса
Рис.6. Результат итогового запроса
2. В итоговые запросы можно включать Условие отбора, причём с применением статистических (групповых) функций.
Пример: вывести список только тех продавцов, стоимость продаж которых больше средней стоимости.
Выполнение:
- Добавить в запрос Итоги условие:
>(SELECT AVG([Стоимость]) From Итоги)
Рис.7. Бланк итогового запроса с условием и результат запроса.
3. Вывести цену самого дорогого товара, проданного каждым продавцом.
Выполнение:
- Ввести в запросный бланк поля ФИО (табл. Продавец), Цена (табл. Товар);
- группы сформировать по полю ФИО из таблицы Продавец ( - Групповая операция);
- в поле Цена задать функцию Мах (максимальное значение поля);
- установить порядок сортировки в поле Цена – по убыванию
- сохранить запрос под именем МахЦена.
Рис.8. Бланк запроса и результат запроса МахЦена
4. Вывести итоговую сумму каждого проданного товара отдельно для каждого продавца.
Выполнение:
- Выполнить запрос на основе запроса «Стоимость» - открыть его в режиме Конструктора;
- включить группировку – - Групповая операция;
- в поле СтоимостьПартии ввести функцию Sum;
·
Рис.9. Бланк итогового запроса (пример 4).
- проверить работу запроса;
- сохранить запрос под другим именем – Файл Сохранить как
Итог_Стоимость.
G Примечание к примеру 4.
1) В примере используется вложенное группирование:
- вначале группируются фамилии продавцов,
- внутри группы совпадающих значений ФИО создаются группы одинаковых названий товаров,
- затем вычисляются суммы по группе товаров.
2) Данный пример является примером многоэтапного запроса, т.е. выполняется запрос к таблице, являющейся результатом предыдущего запроса (1-й этап – запрос «Стоимость», 2-й этап – группирование данных в таблице запроса «Стоимость», результат – запрос «Итог_Стоимость»).