MySQL : subtotals grouped by year-CodePudding

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

CodePudding user response：

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