Saturday, February 21, 2009

Retrieve number of records in a table having column data as NULL

SQL statement that would retrieve the number of records having 'NAME' as NULL

Sample table that has NULL value for NAME column.
SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

Incorrect Solutions:
SQL> select count(name) from mytab where name=NULL;

COUNT(NAME)
-----------
0

SQL> select count(name) from mytab where name IS NULL;

COUNT(NAME)
-----------
0

Correct Solution:
SQL> select count(nvl(name, 0)) from mytab where name is null;

COUNT(NVL(NAME,0))
------------------
2

No comments:

Post a Comment