2.1 Предложение group by.

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

Синтаксис:

SELECT column, group_function(column)

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDERBYcolumn];

где group_by_expression- выражение группировки, которое задаёт столбцы, на основе значений которых группируются строки.

Указания.

1. Все столбцы в предложении SELECT, к которым не применяются функции агрегирования, должны быть указаны в предложенииGROUP BY;

2. Предложение WHEREпозволяет исключать строки до их группировки;

3. Список столбцов в предложении GROUP BYобязателен;

4. Использование псевдонимов столбцов в предложении GROUP BYзапрещено;

Пример 1.

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id ;

Пример 2.Такое использование так же является корректным:

SELECT AVG(salary)

FROM employees

GROUP BY department_id ;

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

Пример 3.

SELECT AVG(salary)

FROM employees

GROUP BY department_id

ORDERBYAVG(salary) ;

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

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

Решение: необходимо сгруппировать строки сначала по отделам, а затем внутри этих групп – по должностям.

Команда SELECT:

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id

ORDER By department_id, job_id;

2.3 Недопустимые примеры использования функций агрегирования

Пример 1

SELECT department_id, COUNT(last_name)

FROMemployees

Сообщение об ошибке:

SELECT department_id, COUNT(last_name)

*

ERROR at line 1:

ORA-00937: not a single-group group function

Пример 2

SELECT department_id, AVG(salary)

FROM employees

WHERE AVG(salary) > 8000

GROUP BY department_id

Сообщение об ошибке:

WHERE AVG(salary) > 8000

*

ERROR at line 3:

ORA-00934: group function is not allowed here

Правила:

1. Предложение WHEREдля исключения групп не используется;

2. Для исключения групп следует использовать предложение HAVING;

3. Нельзя использовать функции агрегирования в предложении WHERE.

3. Исключение групп

Подобно тому, как предложение WHEREиспользуется для исключения строк, предложениеHAVINGиспользуется для исключения групп.

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDERBYcolumn];

где group_condition– ограничивающее условие, которое задаёт для групп строк условие, при выполнении которого они попадают в конечный результат.

Сервер Oracleобрабатывает предложениеHAVINGследующим образом:

1. Строки группируются

2. К группе применяются функции агрегирования

3. Выводятся группы, удовлетворяющие критериям в предложении HAVING.

Предложение HAVING может предшествовать предложению GROUP BY, но логичнее указать предложение GROUP BY первым.

3.1 Использование предложения having

Пример 1

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id

HAVING MAX(salary) > 10000 ;

Результат:

DEPARTMENT_ID MAX(SALARY)

------------- -----------

100 12000

30 11000

90 24000

20 13000

110 12000

80 14000

Пример 2

SELECT job_id, SUM(salary) PAYROLL

FROM employees

WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id

HAVING SUM(salary) > 13000

ORDER BY SUM(salary);

Результат:

JOB_ID PAYROLL

---------- ----------

PU_CLERK 13900

AD_PRES 24000

IT_PROG 28800

AD_VP 34000

ST_MAN 36400

FI_ACCOUNT 39600

ST_CLERK 55700

SA_MAN 61000

SH_CLERK 64300