count(*) with NULLs involved
From: Charlie Crissman <ccriss_at_unf.edu>
Date: 1996/11/01
Message-ID: <327A3C79.3890_at_unf.edu>#1/1
Date: 1996/11/01
Message-ID: <327A3C79.3890_at_unf.edu>#1/1
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.
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