Subtracting previous row using mysql update-CodePudding

Table in database

date	amount	name	id
12-01	10.00	john doe	1002
12-02	10.00	john doe	1003
11-01	50.00	john doe	8976
11-02	50.00	john doe	8977
09-01	50.00	john doe	6788
09-02	50.00	john doe	6799
09-02	50.00	alicia doe	6800

Result should be like this:

Month	amount	total increase/decrease	name
September	100.00	100.00	john doe
November	100.00	0	john doe
December	20.00	-80.00	john doe

CodePudding user response：

you can do it with a query like this

SELECT a1.pdate
 , SUM(a1.amount) AS amount
 , SUM(a1.total) AS total
 , a1.username AS username
FROM (
  SELECT a.*, COALESCE(a.amount LAG(amount) OVER (ORDER BY pdate),0) AS total
  FROM am a 
  ORDER BY pdate
) AS a1
GROUP BY username,MONTH(pdate);

sample

MariaDB [bernd]> SELECT * from am;
 ---- ------------ -------- ---------- 
| id | pdate      | amount | username |
 ---- ------------ -------- ---------- 
|  1 | 2021-12-01 |  10.00 | john doe |
|  2 | 2021-12-02 |  10.00 | john doe |
|  3 | 2021-11-01 |  50.00 | john doe |
|  4 | 2021-11-02 |  50.00 | john doe |
 ---- ------------ -------- ---------- 
4 rows in set (0.02 sec)

MariaDB [bernd]> SELECT a1.pdate
    ->  , SUM(a1.amount) AS amount
    ->  , SUM(a1.total) AS total
    ->  , a1.username AS username
    -> FROM (
    ->   SELECT a.*, COALESCE(a.amount LAG(amount) OVER (ORDER BY pdate),0) AS total
    ->   FROM am a 
    ->   ORDER BY pdate
    -> ) AS a1
    -> GROUP BY username,MONTH(pdate);
 ------------ -------- -------- ---------- 
| pdate      | amount | total  | username |
 ------------ -------- -------- ---------- 
| 2021-11-01 | 100.00 | 100.00 | john doe |
| 2021-12-01 |  20.00 |  80.00 | john doe |
 ------------ -------- -------- ---------- 
2 rows in set (0.00 sec)

MariaDB [bernd]>

CodePudding user response：

It seems like this is what you're looking for:

SELECT MONTHNAME(Last_trans) AS Month,
       name,
       total,
       prevTotal,
       total-IFNULL(prevTotal,0) AS Diff
FROM
(SELECT MAX(date) Last_trans,
       MONTH(date) mth,
       name,
       SUM(amount) total,
       LAG(SUM(amount)) OVER (PARTITION BY name ORDER BY MONTH(date)) prevTotal
FROM mytable
GROUP BY MONTH(date), name) V;

Demo fiddle