PSQL Group by multiple conditions on same row

Question

Let's say I have a table like this:

Letter  Color  
A       Red    
A       Blue    
B       Red          
C       Red
C       Red

What I would like to achieve is the below output format:

Letter   Red   Blue
A        1     1
B        1     0
C        2     0

Tried below:

SELECT letter, red, blue FROM (
SELECT letter, count(*) AS red from letters where color = 'red'
GROUP BY letter
UNION
SELECT letter, count(*) AS blue from letters where color = 'blue'
GROUP BY letter
) GROUP BY letter

Can anyone help? Please note that I can't add any tablefunc or similar to DB. Thanks in advance

Possible duplicate of [PostgreSQL Crosstab Query](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query) — Serg, Jun 04 '19 at 08:05

score 1 · Answer 1 · answered Jun 04 '19 at 11:34

1

I think the simplest method is conditional aggregation:

select letter,
       sum(case when color = 'Red' then 1 else 0 end) as red,
       sum(case when color = 'Blue' then 1 else 0 end) as blue
from letters
group by letter
order by letter;

answered Jun 04 '19 at 11:34

Gordon Linoff

1,242,037
58
646
786

Arulkumar · Accepted Answer · 2019-06-04T11:45:13.440

You can UNION with SELECT as red, blue values and get the MAX() values of red, blue GROUP BY letter

SELECT letter, MAX(red) AS red, MAX(blue) AS blue 
FROM (
    SELECT letter, count(*) AS red, 0 AS blue 
    FROM letters 
    WHERE color = 'red'
    GROUP BY letter
    UNION
    SELECT letter, 0 AS red, count(*) AS blue 
    FROM letters 
    WHERE color = 'blue'
    GROUP BY letter
) alias_name 
GROUP BY letter

score 0 · Answer 3 · answered Jun 04 '19 at 08:16

You can try group on Letter to get counts separately and then join

select T1.Letter, CASE WHEN Red is NULL THEN 0 ELSE Red END AS Red , CASE WHEN Blue is NULL THEN 0 ELSE Blue END AS Blue   
from 
(select Letter, count(*) as Red from letters where Color = 'Red' group by Letter) T1 
full join 
(select Letter, count(*) as Blue from letters where Color = 'Blue' group by Letter) T2 
on T1.Letter = T2.Letter;

PSQL Group by multiple conditions on same row

3 Answers3