Recipe3.10.Performing Outer Joins when Using Aggregates

Recipe 3.10. Performing Outer Joins when Using Aggregates

Problem

Begin with the same problem as in 3.9, but modify table EMP_BONUS such that the difference in this case is not all employees in department 10 have been given bonuses. Consider the EMP_BONUS table and a query to (ostensibly) find both the sum of all salaries for department 10 and the sum of all bonuses for all employees in department 10:

  select * from emp_bonus      EMPNO RECEIVED          TYPE ---------- ----------- ----------       7934 17-MAR-2005          1       7934 15-FEB-2005          2  select deptno,        sum(sal) as total_sal,        sum(bonus) as total_bonus   from ( select e.empno,        e.ename,        e.sal,        e.deptno,        e.sal*case when eb.type = 1 then .1                   when eb.type = 2 then .2                   else .3 end as bonus   from emp e, emp_bonus eb  where e.empno  = eb.empno    and e.deptno = 10        )  group by deptno  DEPTNO  TOTAL_SAL TOTAL_BONUS  ------ ---------- -----------      10       2600         390 

The result for TOTAL_BONUS is correct, but the value returned for TOTAL_SAL does not represent the sum of all salaries in department 10. The following query shows why the TOTAL_SAL is incorrect:

  select e.empno,        e.ename,        e.sal,        e.deptno,        e.sal*case when eb.type = 1 then .1                   when eb.type = 2 then .2              else .3 end as bonus   from emp e, emp_bonus eb  where e.empno = eb.empno    and e.deptno = 10     EMPNO ENAME          SAL     DEPTNO      BONUS --------- ---------  ------- ---------- ----------      7934 MILLER        1300         10        130      7934 MILLER        1300         10        260 

Rather than sum all salaries in department 10, only the salary for "MILLER" is summed and it is erroneously summed twice. Ultimately, you would like to return the following result set:

 DEPTNO TOTAL_SAL TOTAL_BONUS ------ --------- -----------     10      8750         390 

Solution

The solution is similar to that of 3.9, but here you outer join to EMP_BONUS to ensure all employees from department 10 are included.

DB2, MySQL, PostgreSQL, SQL Server

Outer join to EMP_BONUS, then perform the sum on only distinct salaries from department 10:

  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 is null then 0 10                   when eb.type = 1 then .1 11                   when eb.type = 2 then .2 12                   else .3 end as bonus 13   from emp e left outer join emp_bonus eb 14     on (e.empno = eb.empno) 15  where e.deptno = 10 16        ) 17  group by deptno 

You can also use the window function 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 is null then 0  9                       when eb.type = 1 then .1 10                       when eb.type = 2 then .2 11                       else .3 12                  end) over 13        (partition by deptno) as total_bonus 14   from emp e left outer join emp_bonus eb 15     on (e.empno = eb.empno) 16  where e.deptno = 10 17        ) x 

Oracle

If you are using Oracle9i Database or later you can use the preceding solution. Alternatively, you can use the proprietary Oracle outer-join syntax, which is mandatory for users on Oracle8i Database and earlier:

  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 is null then 0 10                   when eb.type = 1 then .1 11                   when eb.type = 2 then .2 12                   else .3 end as bonus 13   from emp e, emp_bonus eb 14  where e.empno  = eb.empno (+) 15    and e.deptno = 10 16        ) 17  group by deptno 

Oracle 8i Database users can also use the SUM OVER syntaxshown for DB2 and the other databases, but must modify it to use the proprietary Oracle outer-join syntax shown in the preceding query.

Discussion

The second query in the "Problem" section of this recipe joins table EMP and table EMP_BONUS and returns only rows for employee "MILLER", which is what causes the error on the sum of EMP.SAL (the other employees in DEPTNO 10 do not have bonuses and their salaries are not included in the sum). The solution is to outer join table EMP to table EMP_BONUS so even employees without a bonus will be included in the result. If an employee does not have a bonus, NULL will be returned for EMP_BONUS.TYPE. It is important to keep this in mind as the CASE statement has been modified and is slightly different from solution 3.9. If EMP_BONUS.TYPE is NULL, the CASE expression returns zero, which has no effect on the sum.

The following query is an alternative solution. The sum of all salaries in department 10 is computed first, then joined to table EMP, which is then joined to table EMP_BONUS (thus avoiding the outer join). The following query works for all DBMSs:

  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,       ( 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         390