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’
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…
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