Friday, September 09, 2005

NULLs in COUNT

Quick, easy one, but it trips up beginners. Something of which to be mindful. Observe:

SELECT COUNT (*) FROM EMP;

COUNT(*)
----------
14

SELECT COUNT (COMM) FROM EMP;

COUNT(COMM)
-----------
4


Note: 4 instead of 14. But if we do this:

SELECT COMM FROM EMP;

COMM
----------

300
500

1400




0





14 rows selected.


We get all 14.

This is expected behaviour. From the Oracle SQL Reference: "If you specify 'expr' then COUNT returns the number of rows where 'expr' is not null."

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

COUNT can take virtually any parameter, including 1, *, or a column. But be aware that if you're counting a particular column, NULLs won't be counted.

Additional reading material:

Everyone knows NULL is one of my favourite topics:
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

Eddie Awad on the difference between COUNT(*) and COUNT(1)
http://awads.net/wp/2005/07/06/count-vs-count1/
(Note: see the links in the comments for more information)

Comments:
Worthwhile pointing out the following quote for user-defined aggregates.

"This routine [ODCIAggregateIterate] is invoked for every non-NULL value in the underlying group. (NULL values are ignored during aggregation and are not passed to the routine.)"

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004615
 
I love this - apparently you can exploit this fact and, combined with the use of DECODE, write some statements more efficiently.

http://www.akadia.com/services/ora_decode.html
 
This comment has been removed by a blog administrator.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?