Having the following data table:
| col1 | days |
|---|---|
| A | 2 |
| B | 3 |
| C | 1 |
| C | 5 |
| D | 3 |
| A | 3 |
| B | 7 |
| A | 4 |
I want to transform it into:
| col1 | <=2days | <=5days | <=7days |
|---|---|---|---|
| A | 1 | 2 | 0 |
| B | 0 | 1 | 1 |
| C | 1 | 1 | 0 |
| D | 0 | 1 | 0 |
I used the below query to achieve this:
select col1,
CASE WHEN days <=2 then count(days) as "<=2days",
CASE WHEN days > 2 and days <=5 then count(days) as "<=5days",
CASE WHEN days > 5 and days <=7 then count(days) as "<=7days"
from tableA group by col1,days
But it returns a result like the following:
| col1 | <=2days | <=5days | <=7days |
|---|---|---|---|
| A | 1 | 0 | 0 |
| A | 0 | 2 | 0 |
| B | 0 | 1 | 0 |
| B | 0 | 0 | 1 |
| C | 0 | 1 | 0 |
| C | 1 | 0 | 0 |
| D | 0 | 1 | 0 |
Can someone please help here?
CodePudding user response：
You could use conditional aggregation as the following:
SELECT col1,
COUNT(CASE WHEN days<=2 THEN 1 END) AS '<=2days',
COUNT(CASE WHEN days<=5 and days>2 THEN 1 END) AS '<=5days',
COUNT(CASE WHEN days<=7 and days>5 THEN 1 END) AS '<=7days'
FROM tableA
GROUP BY col1
ORDER BY col1
See a demo on MySQL.