How to get count of each column in a SQL(Postgres)

Question

I have data like below and trying to get the count of each column per row . How to do it in a SQL/ POSTGRESQL ?

Movie	Horror	Comedy
AAA	Y	Y
bbb	N	N
CCC	Y	N
DDD	Y	Y

How to get the count of each column with a Y in POSTGRES with the expected output like this

Category	count
Horror	3
Comedy	2

Gordon Linoff · Accepted Answer · 2021-08-25 20:14:08Z

3

Probably the simplest method is union all:

select 'Horror', count(*)
from t
where horror = 'Y'
union all
select 'Comedy', count(*)
from t
where Comedy = 'Y';

But in Postgres, I would instead suggest a lateral join:

select genre, count(*)
from t cross join lateral
     (values ('Horror', horror), ('Comedy', comedy)
     ) v(genre, flag)
where flag = 'Y'
group by genre;

answered Aug 25, 2021 at 20:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

NK1 Over a year ago

Thanks! I was looking for the POSTGRES solution. That worked

Collectives™ on Stack Overflow

How to get count of each column in a SQL(Postgres)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related