How can I count the number of zeros in all columns for a table in oracle sql

Suppose you have a database table named db_table1 with hundreds of columns and most of them contain lots of 0’s.

I’m trying to count zeros for each column and divide it by the length of column so I can see the ratio of zeros in each column.

The below code gives me the ratio of nulls in each column. But I couldn’t revise it to count 0’s instead of nulls

Any help will be appreciated

SELECT column_name, (num_nulls/ (SELECT COUNT(*) FROM db_table)*100)
FROM all_tab_columns 
WHERE table_name='table’

Sample data:

col_1	col_2	col_3
0.000	0.000	0.000
0.000	0.000	14.857
3.548	0.000	0.000
0.000	0.000	0.000
0.000	0.000	0.000
0.000	11.586	0.000

Expected Output:

All_Columns	Ratio_of_Zeros
col_1	65.5
col_2	73.5
col_3	48.6

Briefly I need the total number of zeros in every column divided by total row number which gives me the percent or ratio of zeros in each column so I can treat zeros like null and eliminate the column if it has more than 60 percent zeros lets say…

Answer

You can use:

WITH AA AS (
select 
col_1,col_2,col_3
from table_name
)

SELECT column_name, COUNT(column_name) zeros FROM
(
SELECT * FROM (

SELECT * FROM AA
UNPIVOT(
    zeros  
    FOR column_name 
    IN ( 
col_1,col_2,col_3
    )
)
) WHERE zeros = 0
) GROUP BY column_name

Advertisement

Answer