Four ways of Calculating Average Value

In the post Calculate Average Value - Watch Out NULL Values, we mentioned that we need to pay attention to how the NULL values are handled by the database. Using the following table as an example (that has two records with NULL values), we will show four ways of calculating average.

SQL> select * from tbl_test2;

        ID      VALUE
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5
         6

6 rows selected.

Using sum(value)/count(*) will return the wrong result.

SQL> select sum(value)/count(*) from tbl_test2; (wrong!)

SUM(VALUE)/COUNT(*)
-------------------
         2.33333333

The following four queries are correct.
Method 1. Using avg() function.

SQL> select avg(value) from tbl_test2;

AVG(VALUE)
----------
       3.5

Method 2. Using sum(value)/count(value) function. Count(value) will ignore NULL values.

SQL> select sum(value)/count(value) from tbl_test2;

SUM(VALUE)/COUNT(VALUE)
-----------------------
                    3.5

Method 3. Using sum(value)/sum(case when value is null then 0 else 1 end).

SQL> select sum(value)/sum(case when value is null then 0 else 1 end) from tbl_test2;

SUM(VALUE)/SUM(CASEWHENVALUEISNULLTHEN0ELSE1END)
------------------------------------------------
                                             3.5

Method 4. Using sum(value)/count(*) and put "not null" condition in where clause.

SQL> select sum(value)/count(*) from tbl_test2 where value is not null;

SUM(VALUE)/COUNT(*)
-------------------
                3.5