MySQL : calculate total and percentage compared to previous year-CodePudding

I have this table structure

 ------------ -------- 
| date       | amount |
 ------------ -------- 
| 2020-05-01 |     10 |
| 2020-10-01 |     15 |
| 2021-03-01 |      9 |
| 2021-09-01 |     10 |
| 2021-12-01 |     15 |
| 2022-06-01 |     19 |
| 2022-08-01 |     25 |
| 2022-09-01 |     13 |
 ---------------------

I would like to calculate the total for each year, and also the percentage from the previous year

SELECT YEAR(p.date) AS year, SUM(p.amount) AS year_total, SUM(p1.amount) AS prev_year, ROUND(SUM(p1.amount)/SUM(p.amount)*100) AS percentage
FROM payments p
LEFT JOIN payments p1
ON YEAR(p1.date) = YEAR(p.date)-1
GROUP BY year
ORDER BY year DESC;

But with this query the result goes crazy... The totals are not correct except for the first year.

 ------ ------------ ----------- ------------ 
| year | year_total | prev_year | percentage |
 ------ ------------ ----------- ------------ 
| 2022 |        171 |       102 |         60 |
| 2021 |         68 |        75 |        110 |
| 2020 |         25 |      NULL |       NULL |
 ------ ------------ ----------- ------------

I guess I have a group by issue but I can't find a solution.

Edit: forgot to mention I am using MariaDB 10

CodePudding user response：

Your query joins each record of one year with each record of the previous year. Afterwards the sum grouped by year is taken. The simplest solution is to use a window function like LAG as Tim Biegeleisen did. If yoh have to use an older version of MySQL, you'll have to calculate the sums before joining.

Sketch:

SELECT c.year, c.total, p.total
  FROM (SELECT year(date) AS year, sum(amount) AS total FROM payments GROUP BY year(date)) c
  LEFT JOIN (SELECT year(date) AS year, sum(amount) AS total FROM payments GROUP BY year(date)) p
    ON p.year = c.year - 1

CodePudding user response：

On MySQL 8 , we can make use of LAG() here:

SELECT YEAR(date) AS year, SUM(amount) AS year_total,
       LAG(SUM(amount)) OVER (ORDER BY YEAR(date)) AS prev_year,
       100.0 * LAG(SUM(amount)) OVER (ORDER BY YEAR(date)) / SUM(amount) AS percentage
FROM payments
GROUP BY YEAR(date)
ORDER BY YEAR(date) DESC;