count(*) with NULLs involved

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

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.

Thanks in advance,

Charlie Crissman
Sr. Oracle DBA
Barnett Bank, Jacksonville, FL 904-464-6526 (ccriss_at_unf.edu) Received on Fri Nov 01 1996 - 00:00:00 CET