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
– Gordon Linoff, Commented Jun 23, 2021 at 12:29

Gordon Linoff · Accepted Answer · 2021-06-24 15:12:39Z

2

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 = ?

edited Jun 24, 2021 at 15:12

answered Jun 23, 2021 at 12:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johannes Over a year ago

Thanks Gordon, this is what i was looking for!

Johannes Over a year ago

Dear Gordon, can you please draft an example how to use the information_schema.columns, to get a dynamic query?

Lalo Quera · Accepted Answer · 2022-11-03 05:12:56Z

0

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;

answered Nov 3, 2022 at 5:12

Lalo Quera

32 bronze badges

Collectives™ on Stack Overflow

SQL / Postgresql count multiple columns with conditions

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related