0

I want to display the number of null values present in each column in a table. something like this.. I have a table called customer and fields in the customer are cust_id,cust_name, cust_add,cust_gender,cust_phone etc

I want output like this

Column name        Number of null values

cust_id                      0
cust_name                    2
cust_add                     5
cust_gender                  3
cust_phone                   5

. . .

and I am using oracle.

TechDo
  • 18,398
  • 3
  • 51
  • 64
PRAS
  • 27
  • 6
  • 1
    http://stackoverflow.com/questions/2841551/how-can-i-count-only-null-values-in-oracle-plsql – slavoo Sep 02 '13 at 07:28

2 Answers2

2

It's very simple - 

SELECT column_name, num_nulls
  FROM all_tab_columns
 WHERE table_name = 'CUSTOMER'; -- Or whatever is your table name.

Read more on Oracle Docs.

Rachcha
  • 8,486
  • 8
  • 48
  • 70
  • That is only a rough estimate. –  Sep 02 '13 at 07:36
  • @a_horse_with_no_name, yes, that's true. Using this query, I believe, we only get the statistics from the last time when the table was analysed. Please correct me if I'm wrong. – Rachcha Sep 02 '13 at 07:43
  • Thanks a lot.. This is exactly what I was trying. – PRAS Sep 02 '13 at 08:22
  • But How do you write a query which with dynamic inserting? the above query will only give proper value once the statistics are updated. – PRAS Sep 02 '13 at 11:47
  • Simple - run `ANALYSE ` before running this query. Otherwise you will have to use dynamic SQL statements in a PL/SQL procedure, through which you will pass a `REF CURSOR` as an `OUT` parameter. That's a long story. `ANALYSE` will do for you. – Rachcha Sep 02 '13 at 13:17
1

Please try to display data as columns:

select 
    sum(case when cust_id is null then 1 else 0 end) cust_id,
    sum(case when cust_name is null then 1 else 0 end) cust_name, 
    sum(case when cust_add is null then 1 else 0 end) cust_add,
    ..... 
FROM
    customer
TechDo
  • 18,398
  • 3
  • 51
  • 64