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