Home > Net >  Subtracting previous row using mysql update
Subtracting previous row using mysql update

Time:12-25

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

  • Related