Count of each column with non-null values in a table - Oracle

Question

I have a table:

table1

col1        col2        col3        col4
a           b           (null)      c
a           b           (null)      c
a           (null)      (null)      c
(null)      b           (null)      (null)
a           b           (null)      (null)
a           b           (null)      (null)

I have about 300 columns in the table. I need to find count of values for each column which are non-null without typing each column name in the table.

The output would be:

column_name         count_of_non_null
col1                5
col2                5
col3                0
col4                3

Is there a way to do that?

´´300 columns?!? Seems like a horrible design. (You need dynamic SQL to do this.) — jarlh, May 28 '19 at 09:01
Yes, for each column, there should be a row in the output table. — dang, May 28 '19 at 09:01
@jarlh - it is a dataset coming from external source. I've not created it :) — dang, May 28 '19 at 09:03
Simply use Alex's solution in the tagged question..use `count(` instead of `count(distinct` — Kaushik Nayak, May 28 '19 at 09:22

score 1 · Accepted Answer · answered May 28 '19 at 09:15

You need a dynamic PL/SQL to write conditional aggregation kind of queries:

select 'col1' col, count(case when col1 is null then 1 end) from table1
union all
select 'col2' col, count(case when col2 is null then 1 end) from table1

Therefore, your PL/SQL code will be along these lines

declare 
  v_cmd varchar2(10000);
begin 
  for c_column in (select column_name from user_tab_columns where table_name = 'table1') loop
    v_counter := v_counter + 1;
    v_cmd := v_cmd || 'select ''' || c_column.column_name  || ''' col, count(case when ' || c_column.column_name || ' is null then 1 end) from table1 union all ';
  end loop;
  execute immediate left(v_cmd, length(v_cmd) - 11);
end;
/

I did not tested it

Ahamed · Answer 2 · 2019-05-28T09:04:45.177

0

I hope you can achieve using the below query.

select col,cnt from (
select col1 col, sum(case when col1 is not null then 1 else 0 end) cnt from tableA group by col1
union all 
select col2 col, sum(case when col2 is not null then 1 else 0 end) cnt from tableA group by col2
union all
select col3 col, sum(case when col3 is not null then 1 else 0 end) cnt from tableA group by col3
union all
select col4 col, sum(case when col4 is not null then 1 else 0 end) cnt from tableA group by col4
)

edited May 28 '19 at 09:04

answered May 28 '19 at 09:03

Ahamed

312
1
8

I need to type al the columns. Is there a way to get all columns without typing it? – dang May 28 '19 at 09:03
Remember that `count(col1)` only counts non-null values. – jarlh May 28 '19 at 09:05

Count of each column with non-null values in a table - Oracle

2 Answers2