Calculating Averages (Part 2)

Go to part 1

(Tested on Oracle 9 but probably not on the database used for part 1.)

In Calculating Averages (Part 1) I ran the following SQL:

select avg(num_rows) from dba_tables

where num_rows is not null;

Since then I have read that the where clause was unnecessary as Oracle does not include null values when it works out averages. I decided to see if this was true. First I checked that there were both null and not null values in the num_rows column in dba_tables:

SQL> select count(*) from dba_tables

  2  where num_rows is null;

  COUNT(*)

----------

       459

SQL> select count(*) from dba_tables

  2  where num_rows is not null;

  COUNT(*)

----------

       366

SQL>

Incidentally, you can combine the two statements above into one by using decode:

SQL> select

  2  decode(num_rows,null,'NULL','NOT NULL') row_count,

  3  count(*)

  4  from dba_tables

  5  group by decode(num_rows,null,'NULL','NOT NULL');

ROW_COUNT   COUNT(*)

--------- ----------

NOT NULL         366

NULL             459

SQL>

Then I calculated the average as before:

SQL> select avg(num_rows) from dba_tables

  2  where num_rows is not null;

AVG(NUM_ROWS)

-------------

   783867.724

SQL>

Finally, I omitted the where clause and the result was the same. This proves that avg ignores null values:

SQL> select avg(num_rows) from dba_tables;

AVG(NUM_ROWS)

-------------

   783867.724

SQL>