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

1 select deptno, avg(sal) as avg sal

2 from emp

3 group by deptno

DEPTNO AVG SAL

10 2916.66667 20 2175 30 1566.66667

Обсуждение

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

create table t2(sal integer)

insert into t2 values (10)

insert into t2 values (20)

insert into t2 values (null)

select avg(sal) select distinct 30/2 from t2 from t2

AVG(SAL) 30/2

15 15

select avg(coalesce(sal,0)) select distinct 30/3 from t2 from t2

AVG(COALESCE(SAL,0)) 30/3

10 10

Функция COALESCE возвратит первое не-NULL значение переданного в нее списка значений. Если значение столбца SAL преобразовать в нуль, среднее изменится. При работе с агрегатными функциями всегда следует продумать, как должны обрабатываться значения NULL.

Вторая часть решения использует оператор GROUP BY (строка 3) для разделения записей служащих на группы на основании принадлежности к тому или иному отделу. GROUP BY обусловливает выполнение агрегатных функций, таких как AVG, и возвращение результата для каждой группы. В данном примере AVG выполняется по одному разу для каждой группы записей служащих каждого отдела.

Кстати, необязательно включать столбцы, используемые в предложении GROUP BY, в список оператора SELECT. Например:

Решение

При поиске самой низкой и самой высокой заработных плат среди всех служащих просто используются функции MIN и MAX соответственно:

1 select min(sal) as min sal, max(sal) as max sal

2 from emp

MIN SAL MAX SAL 800 5000

При поиске самой низкой и самой высокой заработных плат для каждого отдела используются функции MIN и MAX в сочетании с оператором GROUP BY:

1 select deptno, min(sal) as min sal, max(sal) as max sal

2 from emp

3 group by deptno

DEPTNO MIN SAL MAX SAL

select avg(sal) from emp group by deptno

AVG(SAL)

2916.66667 2175 1566.66667

По-прежнему выполняется группировка по столбцу DEPTNO, хотя он не указан в списке SELECT. Включение столбца, по которому проводится группировка, в оператор SELECT делает код более понятным, но не является обязательным. Однако необходимо, чтобы все столбцы списка SELECT запроса GROUP BY были указаны в операторе GROUP BY.

См. также

Приложение А для повторения функциональных возможностей GROUP BY.

Поиск минимального/максимального значения столбца

Задача

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

Обсуждение

При поиске наибольшего или наименьшего значений во всей таблице к соответствующему столбцу просто применяется функция MIN или MAX без использования оператора GROUP BY.

Не забывайте, что функции MIN и MAX игнорируют значения NULL. Для заданной группы в рассматриваемом столбце могут содержаться как отдельные значения NULL, так и только значения NULL. Далее представлены примеры, в последнем из которых используется оператор GROUP BY, возвращающий значения NULL для двух групп (DEPTNO 10 и 20):

select deptno, comm

from emp

where deptno in (10,30)

order by 1

DEPTNO

COMM

10 10 10

300 500

1300

select min(comm), max(comm) from emp

MIN(COMM) MAX(COMM)

1300

select deptno, min(comm), max(comm) from emp group by deptno

DEPTNO MIN(COMM) MAX(COMM)

10 20 30

1300

Как указано в Приложении А, даже если в операторе SELECT, кроме агрегатной функции, не указано никаких столбцов таблицы, группировку можно осуществлять по другим столбцам. Например: