SQL / Postgresql count multiple columns with conditions

Question

I have a simple table of the form:

id	gender	a_feature (bool)	b_feature (bool)	...	xyz_feature (bool)

and I want to sum over all feature columns dependent on gender.

metric	male	female
a_feature	345	3423
b_feature	65	143
...	...	...
xyz_feature	133	5536

Is there a simple way to do this, e.g. using the information_schema.

I found only the solution below, but this is very ugly:

select
       'a_feature' as feature_name,
       count(case a_feature and gender = 'male') as male,
       count(case a_feature and gender = 'female') as female
from table
union
select
       b_feature as feature_name,
       count(case b_feature and gender = 'male') as male,
       count(case b_feature and gender = 'female') as female
from table
.
.
.
select
       xyz_feature as feature_name,
       count(case xyz_feature and gender = 'male') as male,
       count(case xyz_feature and gender = 'female') as female
from table

Tag your question with the database you are using. The code you have is not syntactically valid either. — Gordon Linoff, Jun 23 '21 at 12:29

Gordon Linoff · Accepted Answer · 2021-06-24T15:12:39.577

You can unpivot and aggregate. One method is:

select name,
       sum(case when feature and gender = 'male' then 1 else 0 end) as num_male,
       sum(case when feature and gender = 'female' then 1 else 0 end) as num_female
from ((select 'a_feature' as name, a_feature as feature, gender
       from t
      ) union all
      (select 'b_feature' as name, b_feature, gender
       from t
      ) union all
      . . .
     ) f
group by name;

In Postgres, you would unpivot using a lateral join:

select name,
       sum(case when feature and gender = 'male' then 1 else 0 end) as num_male,
       sum(case when feature and gender = 'female' then 1 else 0 end) as num_female
from t cross join lateral
     (values ('a_feature', a_feature),
             ('b_feature', b_feature),
             . . .
     ) v(name, feature)
group by name;

You can generate the list for values() using information_schema.columns if you are reluctant to type it all in.

EDIT:

You can construct the values clause using something like this:

select string_agg('(''' || column_name || ''', column_name)', ', ') 
from information_schema.columns
where table_name = ?

Dear Gordon, can you please draft an example how to use the information_schema.columns, to get a dynamic query? — Johannes, Jun 24 '21 at 14:15

score 0 · Answer 2 · answered Nov 03 '22 at 05:12

When you use this in Postgres, what do you mean by t (I think t is for table) in "from t cross join lateral" and what do you mean by v in "v(name, feature)" ?

select name,

           sum(case when feature and gender = 'male' then 1 else 0 end) as num_male,
           sum(case when feature and gender = 'female' then 1 else 0 end) as num_female
    from t cross join lateral
         (values ('a_feature', a_feature),
                 ('b_feature', b_feature),
                 . . .
         ) v(name, feature)
    group by name;

SQL / Postgresql count multiple columns with conditions

2 Answers2