MySQL : calculate total and percentage compared to previous year

user_3973138 0
Viewed 35

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

group-by mysql
2 Answers

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;
user_18632290

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
user_196571830
Related