I want to get the count values of every non-null item from each column of the table. The table contains 86 columns and I want the output in the tabular format having:

column_name | count

Any help in this regard is appreciated. Thanks

CodePudding user response：

Here is an illustration of my suggestion.
The first part of the query (t CTE definition) is table-specific as it needs all column names to be listed. I hope that this would be trivial with the help of a proper SQL client. The rest is generic.

create temporary table the_table (x integer, y integer, z integer);
insert into the_table
values 
(null, 1, 1), (null, 2, 2), (3, 3, 3), (4, null, 4), 
(5, 5, 5),  (6, null, 6), (7, 7, null), (null, 8, 8);

with t(x, y, z) as 
(
 select count(x), count(y), count(z) from the_table
),  
u as 
(
 select l.* 
 from t 
 cross join lateral jsonb_each_text(to_jsonb(t)) l
)
select 
    key as column_name, value::integer as count_of_values
from u;

CodePudding user response：

You can of course simply do:

select 'col1' as column_name, count(col1) from mytable
union all
select 'col2' as column_name, count(col2) from mytable
union all
...
select 'col86' as column_name, count(col86) from mytable;

(This is easily written. You can put the table list in Excel for instance, apply a concatenation formula and thus get the whole query in no time.)

CodePudding user response：

You can dynamically convert all columns to rows using to_jsonb() and then group and count per column name:

select j.column_name, count(j.value)
from the_table t
  cross join lateral jsonb_each_text(jsonb_strip_nulls(to_jsonb(t))) as j(column_name, value)
group by j.column_name
order by j.column_name;

Online example