MySQL : subtotals grouped by year

Question

I have this table

+------------+---------+--------+
|       date |    item | amount |
+------------+---------+--------+
| 2022-01-12 | PA-1235 |    100 |
| 2022-01-07 | PA-1234 |     50 |
| 2022-01-07 |  RE-123 |     10 |
| 2021-12-22 | PA-1233 |    100 |
| 2021-11-21 | PA-1232 |    150 |
| 2021-11-15 |  RE-122 |     10 |
| 2021-11-13 |  RE-121 |     10 |
+------------+---------+--------+

I would like the total per year so I've done

SELECT YEAR(date) as year, SUM(amount) AS total FROM payments GROUP BY YEAR(date) ORDER BY YEAR(date) DESC

+------+-------+
| year | total |
+------+-------+
| 2022 |   160 |
| 2021 |   270 |
+------+-------+

But know I'de like to have the subtotals for items starting with PA

+------+-----+-------+
| year |  PA | total |
+------+-----+-------+
| 2022 | 150 |   160 |
| 2021 | 250 |   270 |
+------+-----+-------+

UNION will add more lines, how to insert subqueries (item LIKE 'PA-%') in a single query?

I can't change the table structure

Akina Akina · Accepted Answer · 2022-01-27T10:29:33

SELECT YEAR(date) AS `year`, 
       SUM(CASE WHEN item LIKE 'PA-%'
                THEN amount
                ELSE 0 
                END) AS PA,
       SUM(amount) AS total 
FROM payments 
GROUP BY `year`
ORDER BY `year` DESC

MySQL : subtotals grouped by year

1 Answers