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

DEPTNO TOTAL SAL TOTAL BONUS 10 8750 2135

Решение

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

MySQL и PostgreSQL

Суммируем только уникальные (DISTINCT) заработные платы:

1 select deptno,

2 sum(distinct sal) as total sal,

3 sum(bonus) as total bonus

4 from (

5 select e.empno,

6 e.ename,

7 e.sal,

8 e.deptno,

9 e.sal*case when eb.type = 1 then .1

10 when eb.type = 2 then .2

11 else .3

12 end as bonus

13 from emp e, emp bonus eb

14 where e.empno = eb.empno

15 and e.deptno = 10

16 ) x

17 group by deptno

DB2, Oracle и SQL Server

Эти платформы поддерживают предыдущее решение, но для них также можно использовать альтернативное решение с применением оконной функции SUM OVER:

1 select distinct deptno,total sal,total bonus

2 from (

3 select e.empno,

4 e.ename,

5 sum(distinct e.sal) over

6 (partition by e.deptno) as total sal,

7 e.deptno,

8 sum(e.sal*case when eb.type = 1 then .1

9 when eb.type = 2 then .2

10 else .3 end) over

11 (partition by deptno) as total bonus

12 from emp e, emp bonus eb

13 where e.empno = eb.empno

14 and e.deptno = 10

15 ) x

Обсуждение

MySQL и PostgreSQL

Второй запрос в разделе Задача данного рецепта осуществляет объединение таблиц EMP и EMP BONUS, в результате чего возвращаются две строки для служащего MILLER, что и обусловливает ошибочность значения суммы в EMP.SAL (заработная плата этого служащего суммируется дважды). Решение состоит в суммировании только уникальных значений EMP.SAL, возвращаемых запросом. Следующий запрос является альтернативным решением. Здесь сначала вычисляется сумма заработных плат всех служащих 10-го отдела, затем эта строка объединяется с таблицей EMP, которая в дальнейшем объединяется с таблицей EMP BONUS. Запрос ниже применим для всех СУБД:

select d.deptno,

d.total sal,

sum(e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end) as total bonus

from emp e,

emp bonus eb, (

Функция для работы с окнами SUM OVER вызывается дважды. В первый раз - для вычисления суммы уникальных заработных плат для заданного сегмента или группы. В данном случае сегмент - это 10-й отдел (DEPTNO 10), и сумма уникальных заработных плат для DEPTNO 10 составляет 8750. Следующий вызов SUM OVER осуществляет вычисление суммы премий того же сегмента. Окончательное результирующее множество (значения столбцов TOTALSAL и TOTALBO-NUS) формируется путем суммирования уникальных значений заработных плат и премий.

select deptno, sum(sal) as total sal

from emp

where deptno = 10

group by deptno ) d

where e.deptno = d.deptno

and e.empno = eb.empno

group by d.deptno,d.total sal

DEPTNO TOTAL SAL TOTAL BONUS

10 8750 2135

DB2, Oracle и SQL Server

В этом альтернативном решении используется оконная функция SUM OVER. Данный запрос занимает строки 3-14 примера раздела Решение и возвращает следующее результирующее множество:

select e.empno,

e.ename,

sum(distinct e.sal) over

(partition by e.deptno) as total sal,

e.deptno,

sum(e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end) over (partition by deptno) as total bonus from emp e, emp bonus eb where e.empno = eb.empno and e.deptno = 10

EMPNO ENAME TOTAL SAL DEPTNO TOTAL BONUS