MySQL : subtotals grouped by year

129
January 29, 2022, at 7:40 PM

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

Answer 1
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
Rent Charter Buses Company
READ ALSO
How to target classes with dynamically added classname in jQuery

How to target classes with dynamically added classname in jQuery

I'm trying to use one piece of code to expand different content when different sections are clicked onThe way I'm trying to achieve this is by giving these tags a class name like class="counter_1", class= "conter_2"

125
Unchecked call to 'getOrDefault(Object, V)' as a member of raw type 'java.util.Map' [closed]

Unchecked call to 'getOrDefault(Object, V)' as a member of raw type 'java.util.Map' [closed]

Want to improve this question? Update the question so it's on-topic for Stack Overflow

145
Change maven-publish url based on actual BuildType (gradle)

Change maven-publish url based on actual BuildType (gradle)

I have followed this tutorial and configured maven-publish plugin to use an private repository

112