Re: count(*) with NULLs involved

In article <327A3C79.3890_at_unf.edu>, Charlie Crissman <ccriss_at_unf.edu> writes
>Oracle DBA Gang,
>
>I am running into something very curious and wondered if anyone else had
>encountered this or had a nice way to work around it:
>
>Let's say we have this table (test_table) with exactly 5,000 rows. One
>column (column_X) is has several possible values ('A','B','C','D',NULL)
>
>Lets say there are 1000 rows where column_X = 'A'
> 500 rows where column_X = 'B'
> 250 rows where column_X = 'C'
> 250 rows where column_X = 'D'
> 3000 rows where column_X is NULL
>
>When I query the table for a count where column_X is neither 'B' nor
>'C', common sense would tell me that I should get an answer of 4250.
>
>I don't get that. I get an answer of 1250. Oracle is not counting the
>nulls in my query.
>
>My query is as follows:
>
>SQL> select count(*) from test_table
> 2> where column_X not in ('B','C');
>
>I don't want to have to replace the NULL's in this column with ' ', but
>if I don't, my clients will make this same query and get an incorrect
>answer.
>
>Any hints would be appreciated.

Your query as stated is saying (assuming column_x is some sort of classification) 'give me rows which are all classificatiosn other than B,C, INCLUDING UNCLASSIFIED'. NULL is not in (B,C). In fact it is never in any set. If the data is fixed as you describe above then

        where column_x in ('A','D')

will give you the correct answer but I suspect its not that simple.

The only good solution is always to include 'and column_x is not null'

Another point which may be useful is that count(*) includes nulls (because it counts rows regardless of content) while count(column_name) excludes them. This is only useful if you are actually counting column_x's as well as using them as selection criteria.

I am afraid your only alternatives are to use another character (spaces as you suggest) to represent the 'unclassified' or to make sure your clients all have a thorough understanding of SQL and the behaviour of NULLs (a snowball's chance of that).

-- 
Jim Smith