What's the easiest way of creating a column counting a categorical variable on PostgreSQL? Maybe some sort of pivoting?

Question

Let's say I have the following table:

Id    color
A00   blue
A00   blue
A99   red
A99   blue
A95   yellow
A97   green

I would like to get something like:

Id    blue    red    yellow    green
A00   2       0      0         0     
A99   1       1      0         0
A95   0       0      1         0
A97   0       0      0         1

What's the easiest way of doing this?

I thought about this:

select Id,
       sum(case when color='blue' then 1 else 0 end) as blue,
       sum(case when color='red' then 1 else 0 end) as red,
       .
       .
       .
from table 

The problem is that I have so many colours that doing this would be exhausting. Is there an easier way?

Answer 1

There are many ways to achieve this:

USING FILTER

select 
id,
count(*) filter (where color='blue') as "Blue",
count(*) filter (where color='red') as "Red",
count(*) filter (where color='yellow') as "Yellow",
count(*) filter (where color='green') as "Green"
 from samp
group by id

Fiddle

Your Method

select 
id,
sum(case when color='blue' then 1 else 0 end) as "Blue",
sum(case when color='red' then 1 else 0 end) as "Red",
sum(case when color='yellow' then 1 else 0 end) as "Yellow",
sum(case when color='green' then 1 else 0 end) as "Green"
 from samp
group by id

Fiddle

Using Crosstab

select * from crosstab(
'select id, color,count(*) from samp group by id,color order by id,color', 
'select distinct color from samp order by color'
) 
as ct("ID" varchar, "blue" int,"green" int,"red" int,"yellow" int);

Note: you have to create extension for crosstab using below query

CREATE EXTENSION IF NOT EXISTS tablefunc;