CODEWITHSUNDEEP
sqloracle-database
PRAS
PRAS

Reputation: 27

display column name and number of null values present in that particular column in oracle

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.

Upvotes: 0

Views: 1056

Answers (2)

TechDo
TechDo

Reputation: 18659

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

Upvotes: 1

Rachcha
Rachcha

Reputation: 8816

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.

Upvotes: 2

Related Questions