Count each null in a table 2

[nickdel]

Is it possible to count every NULL in a table? I have a large table and users are asking how well it's populated.

Thanks

nick

where would we be without rhetorical questions...

 

[hoinz]

What exactly is your problem?
It's easy to find the number of NULLs in a given column:
select count(*) from <table> where <column> is NULL;

It may be a bit hard, if you have many columns, and want this number for all columns, or if you want just one number for the whole table, no matter in what column the NULLS are...

 

[nickdel]

hoinz, basically your last statement is what I am looking for. The total number of nulls throughout the whole table. The table has about 200 columns so I thought some sort of loop but I'm not really that great with PL/SQL as I've only used SQL server before.

where would we be without rhetorical questions...

 

[karluk]

You should be able to do this in a single SQL statement:

select sum(nvl2(col001, 0, 1))
     + sum(nvl2(col002, 0, 1))
     + sum(nvl2(col003, 0, 1))
...
     + sum(nvl2(col200, 0, 1))
from your_table;

Naturally, the difficult thing about this is to assemble the 200 or so sum(nvl2(...)) expressions, since you have to replace "col001", "col002" and so forth with actual column names. You can get this information from the Oracle catalog with the following query. Cut and paste the output into your query, remembering to remove the "+" sign from the first line.

select '+ sum(nvl2(' || column_name || ',0,1))' from user_tab_columns
    where table_name = 'YOUR_TABLE' and nullable = 'Y';

 

[sangisiva]

Hi

From karluk code..this procedure would give you total null in the table for all columns...

DECLARE
     TotalNull number(5):=0;
     column_count number(5);
     v_null number(5);
     sqlstr varchar2(300);

     CURSOR col_cur IS
        select column_name from user_tab_columns where      
        table_name=<Your Table Name>;
BEGIN
   For nullrec in col_cur loop
     sqlstr:='select count(*) from <Your Table Name> where   
       '||nullrec.column_name||' is null';
     EXECUTE IMMEDIATE sqlstr into v_null;
     TotalNull:=TotalNull+v_null;
   end loop;
   dbms_output.put_line('Number of Nulls in the Table' || TotalNull);
END;