ColorBreakdown
| id | color | amount |
|---|---|---|
| 0 | red | 25 |
| 0 | blue | 13 |
| 1 | red | 16 |
| 1 | blue | 17 |
Color
| name |
|---|
| red |
| blue |
Given the tables above, how do I separate it into the table below without altering said table?
(only by selecting, joining, filtering etc.)
| id | red | blue |
|---|---|---|
| 0 | 25 | 13 |
| 0 | 16 | 17 |
I am totally lost as to how I would even go about this.
I have tried doing
SELECT id, c.amount
FROM Color
INNER JOIN ColorBreakdown AS c ON (Color.name = ColorBreakdown.color);
just to mess around but it only gives the red color for each id.
| id | c.amount |
|---|---|
| 0 | 25 |
| 0 | 16 |
Then I tried
SELECT id,
SUM(color = 'red') AS red,
SUM(color = 'blue') AS blue
FROM ColorBreakdown
GROUP BY id;
but it gave
| id | red | blue |
|---|---|---|
| 0 | 1 | 1 |
| 0 | 1 | 1 |
I am pretty lost 🙁
>Solution :
You need to use group by and case to achieve this
a sample query is something like :
SELECT id,
sum(case when color = 'red' then amount else 0 end) AS red,
sum(case when color = 'blue' then amount else 0 end) AS blue
FROM ColorBreakdown
group by id;