MySQL calculate points related to transaction balances over different days and need the difference
Sample Data
| id | transaction_date | points |
|---|---|---|
| 1 | 09-08-2023 | 10 |
| 2 | 09-08-2023 | -5 |
| 3 | 09-08-2023 | 8 |
| 4 | 09-08-2023 | -3 |
| 5 | 09-08-2023 | 15 |
| 6 | 09-08-2023 | -8 |
| 7 | 09-08-2023 | 5 |
| 8 | 09-08-2023 | -2 |
| 9 | 09-08-2023 | 12 |
| 10 | 09-08-2023 | -6 |
| 11 | 10-08-2023 | 11 |
| 12 | 10-08-2023 | -4 |
| 13 | 10-08-2023 | 7 |
| 14 | 10-08-2023 | -3 |
| 15 | 10-08-2023 | 13 |
| 16 | 10-08-2023 | -7 |
| 17 | 10-08-2023 | 6 |
| 18 | 10-08-2023 | -2 |
| 19 | 10-08-2023 | 10 |
| 20 | 10-08-2023 | -5 |
| 21 | 11-08-2023 | 9 |
| 22 | 11-08-2023 | -4 |
| 23 | 11-08-2023 | 8 |
| 24 | 11-08-2023 | -3 |
| 25 | 11-08-2023 | 14 |
| 26 | 11-08-2023 | -7 |
Result Expected
| transaction_date | total points | difference |
|---|---|---|
| 09-08-2023 | 26 | NULL |
| 10-08-2023 | 26 | 0 |
| 11-08-2023 | 17 | -9 |
>Solution :
Try This
SELECT
transaction_date,
SUM(points) AS sum_of_points,
SUM(points)-LAG(SUM(points), 1) OVER (ORDER BY transaction_date) AS points_difference_previous_day
FROM
transactions
GROUP BY
transaction_date
ORDER BY
transaction_date;