I have a table that includes multiple columns (more than 200 cols) and all values in all cols are either 0 or 1, like the following:

What I want to do is to count all of the zero and one values for each columns and display them row-wise like the following:

I am trying to write my query like the following:

select 'col1', 
       sum(case when col1=1 then 1 else 0 end) one_values, 
       sum(case when col1=0 then 1 else 0 end) zero_values
from t
union all
select 'col2', 
       sum(case when col2=1 then 1 else 0 end) one_values, 
       sum(case when col2=0 then 1 else 0 end) zero_values
from t

however, since I have too many columns, I would get the resource exceed error. I was wondering if anyone can suggest a more efficient way to do this?

Thank you

CodePudding user response：

Use below approach

select split(kv, ':')[offset(0)] as column, 
  countif(1 = safe_cast(split(kv, ':')[offset(1)] as int64)) as one_values,
  countif(0 = safe_cast(split(kv, ':')[offset(1)] as int64)) as zero_values
from your_table t, unnest(split(translate(to_json_string(t), '{}"', ''))) kv
group by column    

if applied to sample data in your question - output is