AVG Aggregate Function and NULL in Oracle Database

In my previous post on Oracle Aggregate Functions, I explained Oracle Database ignores NULL values with Aggregate Functions, which is perfect while calculating COUNT, MIN, MAX and SUM but sometimes it leads us to wrong values if we do not focus on Oracle Database behavior and business requirements especially while calculating AVERAGE.

With this post I am trying to explain the behavior of AVG aggregate function on column having NULL values, and how it may lead us to incorrect output. First let us check following data from EMP table.

NGARG> select * from emp where deptno in (select deptno from dept where dname = 'SALES');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
6 rows selected.

Now Business asks, what is the average commission for Sales department.

As we can see, there are 6 employees in Sales department, 4 employees are eligible for commission, 3 employees got commission and total commission given to employees in sales department is 2200. Now depending on business requirement, they might be asking for
1. "total commission"/"number of employees" which gives us 366.67 average commission.
2. "total commission"/"number of employees eligible for commission" which gives us 550 average commission.
3. "total commission"/"number of employees who received commission" which gives us 733.33 average commission.

NGARG> select count(*), sum(comm), sum(comm)/count(*), avg(comm), sum(comm)/count(nullif(comm,0)) from emp 
       where deptno in (select deptno from dept where dname = 'SALES');

  COUNT(*)  SUM(COMM) SUM(COMM)/COUNT(*)  AVG(COMM) SUM(COMM)/COUNT(NULLIF(COMM,0))
---------- ---------- ------------------ ---------- -------------------------------
         6       2200         366.666667        550                      733.333333

As you might have noticed, AVG function has ignored all rows having NULL values in COMM column and calculated average by dividing "total commission" (2200) by number of rows having NOT NULL values in COMM column (4) and resulted in 550 average commission for department SALES. This behavior of Oracle Database might not be what business is looking for.

With following statement I wanted end this post "In Oracle Database NULL is represented as NO VALUE, which is very different from 0 (Zero) value." I hope you have enjoyed the post, please add your feedback in comments.


Related Posts -
- Oracle Aggregate Functions - Count Sum Avg Min Max
- SQL Interview Question Answers
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- ROUND, TRUNC, CEIL and FLOOR in Oracle Database
- Difference between MOD and REMAINDER function
- Order By and Null values in Oracle Database
- Oracle: Some Important Date Queries
- Playing With Truncate and Date in Oracle Database
- NOT IN clause with NULL is tricky in Oracle Database
- Rollup and Grouping Sets in Oracle Database