Tuesday, April 05, 2011

Calculating Averages (Part 2)

CatholicSingles.com-Meet Singles With Like Faith Values!  468x60 Banner

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>

No comments: