This is table.
| idx | statusIdx | date |
|---|---|---|
| 1 | 1 | 2022-05-12 02:24 |
| 2 | 2 | 2022-05-12 02:24 |
| 3 | 3 | 2022-06-12 02:24 |
| 4 | 1 | 2022-07-12 02:24 |
| 5 | 1 | 2022-07-12 02:24 |
| 6 | 2 | 2022-07-12 02:24 |
How can i get like this?
| stauts1 | status2 | status3 | |
|---|---|---|---|
| 2022-05 | 1 | 1 | 0 |
| 2022-06 | 0 | 0 | 1 |
| 2022-07 | 2 | 1 | 0 |
| sum | 3 | 2 | 1 |
I've tried to use row_number() and count() over().
SELECT
FDATE, cnt1, cnt2, cnt3
FROM
(SELECT
DATE_FORMAT(date, "%Y.%m") AS FDATE,
COUNT(case when statusIdx=1 then 1 end) over(PARTITION BY date, statusIdx) AS cnt1,
COUNT(case when statusIdx=2 then 1 end) over(PARTITION BY date, statusIdx) AS cnt2,
COUNT(case when statusIdx=3 then 1 end) over(PARTITION BY date, statusIdx) AS cnt3,
row_number() over (PARTITION by date, statusIdx) AS rnk
FROM transfer
WHERE date betWEEN "20212-05-01" AND "2022-07-31"
ORDER BY FDATE ASC
) t
WHERE rnk = 1
It show vertically counting.
| num | FDATE | cnt1 | cnt2 | cnt3 |
|---|---|---|---|---|
| 1 | 2022-05 | 1 | 0 | 0 |
| 1 | 2022-05 | 0 | 1 | 0 |
| 3 | 2022-06 | 0 | 0 | 1 |
| 4 | 2022-07 | 2 | 0 | 0 |
| 5 | 2022-07 | 0 | 1 | 0 |
How to aggregation without group_by in mysql?
CodePudding user response：
Used sum() over partition. see dbfidde link if this works.