Recipe7.12.Aggregating Nullable Columns

Recipe 7.12. Aggregating Nullable Columns

Problem

You want to perform an aggregation on a column, but the column is nullable. You want the accuracy of your aggregation to be preserved, but are concerned because aggregate functions ignore NULLs. For example, you want to determine the average commission for employees in DEPTNO 30, but there are some employees who do not earn a commission (COMM is NULL for those employees). Because NULLs are ignored by aggregates, the accuracy of the output is compromised. You would like to somehow include NULL values in your aggregation.

Solution

Use the COALESCE function to convert NULLs to 0, so they will be included in the aggregation:

 1 select avg(coalesce(comm,0)) as avg_comm 2   from emp 3  where deptno=30 

Discussion

When working with aggregate functions, keep in mind that NULLs are ignored. Consider the output of the solution without using the COALESCE function:

  select avg(comm)   from emp  where deptno=30  AVG(COMM)  ---------        550 

This query shows an average commission of 550 for DEPTNO 30, but a quick examination of those rows:

  select ename, comm   from emp  where deptno=30 order by comm desc ENAME           COMM ---------- --------- BLAKE JAMES MARTIN          1400 WARD             500 ALLEN            300 TURNER             0 

shows that only four of the six employees can earn a commission. The sum of all commissions in DEPTNO 30 is 2200, and the average should be 2200/6, not 2200/4. By excluding the COALESCE function, you answer the question, "What is the average commission of employees in DEPTNO 30 who can earn a commission?" rather than "What is the average commission of all employees in DEPTNO 30?" When working with aggregates, remember to treat NULLs accordingly.