Однотабличные итоговые запросы

Итоговые запросы позволяют вычислять значения для групп данных, «подводить итоги» по каждой группе записей. Например, вычислить по каждому отделу суммарную зарплату, найти максимальную премию для каждой должности и т.п.

Группы имеет смысл образовывать, если какое-либо поле в каждой группе имеет одно и то же значение, по этому полю и идет группировка. Например, группировка по полю Отдел: создаются группы записей, в каждой из которых значение этого поля (номер отдела) одинаково. В таблице «МоиСотрудники» три отдела, поэтому групп по этому полю будет также три: в первую группу попадут все записи (кортежи целиком!) с номером отдела 1, во вторую – все записи с номером отдела 2, в третью – с номером отдела 3. Если группировка идет, например, по полю Должность, то группы образуют записи с одинаковым значением должности, групп будет столько, сколько разных должностей в таблице.

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

Если функция указана, а поле группировки – нет, то группой считается вся таблица, и итог вычисляется для всего столбца аргументов функции (например, Sum([Начислено])).

Если же в качестве поля группировки указан столбец, не имеющий повторяющихся значений, например, ФИО, то каждая запись таблицы тогда образует группу и итоговые вычисления теряют смысл (например, функция Sum([Начислено]) даст значение, равное значению поля Начислено для каждой записи).

 

Пример 7. Укажите для каждого отдела среднюю зарплату и количество сотрудников в нем.

Шаги выполнения:

1. Откройте новый запрос в режиме конструктора. Поместите в бланк запроса поля, участвующие в данном запросе: Отдел (поле группировки), Начислено (поле для вычисления зарплаты), ФИО (поле для вычисления количества сотрудников) из таблицы «Сотрудники».

2. На панели инструментов нажмите кнопку Групповые операции или войдите в меню Вид → Групповые операции. В результате в бланке запроса появится строка Групповая операция, и для всех полей в данной строке будет установлен вариант Группировка.

3. В поле Начислено в строке Групповая операция замените, используя выпадающий список, вариант Группировка на функцию вычисления среднего - Avg, а в поле ФИО - на функцию вычисления количества - Count.

Обязательные действия закончены, запрос можно выполнить. Однако результирующая таблица будет иметь «некрасивые» заголовки столбцов, соответствующих вычисленным итоговым значениям, и слишком «длинные» числа (выполните запрос и убедитесь). Поэтому отформатируем результат:

4. Измените названия полей Начислено и ФИО в бланке запроса на Средняя зарплата и Количество работников. Для этого во втором столбце бланка запроса перед словом Начислено наберите Средняя зарплата и поставьте двоеточие, нажмите Enter. Аналогичные действия выполните для третьего столбца

5. Поставьте курсор во второй столбец бланка запроса и измените свойства поля, задав Формат поля - Денежный.

6. Запустите на выполнение созданный запрос.

þ Функция Count на самом деле считает количество записей в таблице.

Задания

12. Посчитайте среднюю зарплату для каждой должности.

13. Укажите, сколько человек работает в отделе, начальником которого является Буйный.

14. Посчитайте среднюю зарплату в отделе, начальник которого - Осина.

15. Укажите максимальную зарплату в каждом отделе.

16. Посчитайте максимальную, минимальную и суммарную зарплату по каждой должности, исключая техника, директора и главного бухгалтера. Замените названия полей в результате запроса.

17. Посчитайте минимальную зарплату в третьем и втором отделах, не включая должность референта.

18. Посчитайте, сколько должностей в каждом отделе.

19. Посчитайте количество сотрудников в первом и втором отделах, исключая Бондарчука, Петровскую и Керичеева.

20. Определите минимальную и суммарную зарплату по отделам для сотрудников, получающих не меньше 3000 р. и работающих не в первом отделе.

21. Определите среднюю и максимальную зарплату в отделах, не учитывая зарплату свыше 16500 р.