О. Ткачев - Основы программирования в СУБД Oracle. SQL+PL/SQL.

SELECT {список столбцов*), {агрегатные функции}

FROM {таблица}

WHERE {условия}

GROUP BY {список столбцов*}

HAVING {условия на группу};

Списки столбцов в предложениях SELECT и GROUP BY должны совпадать.

Предложение GROUP BY разбивает данные на группы, и запрос выводит обобщенные данные о каждой группе.

Рассмотрим примеры задач, для решения которых необходимо использовать группировку и агрегатные функции.

Пример 4.7. Для каждого отдела определить суммарную зарплату

SELECT department_id, SUM (salary) AS SUM_salary

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Пример 4.8. Для каждого отдела определить суммарную зарплату с учетом комиссионных

SELECT department_id, SUM (salary* (1+NVL (commission_pct,0)))

As sum_sal

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Пример 4.9. Для каждого отдела определить суммарную длину имен (столбца first_name)

SELECT department_id, SUM (LENGTH (first_name)) As sum_f_nam

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Группировка по нескольким столбцам

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

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

Пример 4.10а. Для каждого отдела определить должности и количество сотрудников, занимающих эту должность (содержит ошибку)

SELECT department_id, job_id, count (*)

FROM employees

GROUP BY department_id;

Причина ошибки: при наличии группировки предложение SELECT может содержать только столбцы, по которым осуществляется группировка и агрегатные функции.

Пример 4.10б. Для отделов 30 и 50 определить должности и количество сотрудников, занимающих каждую должность

SELECT department_id, job_id, count (*)

FROM Employees

WHERE department_id IN (30,50)

GROUP BY department_id, job_id

ORDER BY department_id;

Пример 4.11. Для отделов, номер которых меньше 50, вывести рейтинги, которые имеют сотрудники этого отдела, их количество и суммарную зарплату

SELECT department_id, rating_e, count (*),sum (salary)

FROM Employees

WHERE department_id <= 50

GROUP BY department_id, rating_e

ORDER BY department_id;

Использование условий на группу

Условия на группу указываются в предложении HAVING.

Пример 4.12. Вывести суммарную зарплату для тех отделов, у которых суммарная зарплата превышает 50 000

SELECT department_id, SUM (salary)

FROM Employees

GROUP BY department_id

HAVING SUM (salary)> 50000;

Условие выборки может быть проверено до группировки. В этом случае сначала выбираются строки, удовлетворяющие условию, а потом осуществляется группировка полученных данных. Для полученных групп можно указать условие в предложении HAVING. В результат запроса попадут только те группы, которые удовлетворяют этому условию.

Пример 4.13. Вывести должности и количество сотрудников, которые получают зарплату более 10 000

SELECT department_id, SUM (salary)

FROM Employees

GROUP BY department_id

HAVING SUM (salary)> 50000;

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

Пример 4.14. Вывести должности и количество сотрудников, которые получают зарплату более 10 000, которые занимают более одного сотрудника, упорядочив их в порядке убывания количества сотрудников