CODEWITHSUNDEEP
postgresql
NK1
NK1

Reputation: 25

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

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

Upvotes: 1

Views: 381

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 3

Related Questions