MySQL calculation of cumulative sum with a reset condition

Question

Is there a way to capture a cummulative sum with a reset condition in mySQL? The use case for this is a simple cash register table

id    transactionType      value  CurrentBalance
1      purchase             10        10
2      sale                -10        0
3      RESET                20        20
4      purchase             10        30
5      sale                -10        20
6      sale                 10        30
7      sale                -20       10

The answer from here is a good starting point but I don't see how to extend it: Create a Cumulative Sum Column in MySQL

SELECT t.id,
         t.count,
         @running_total := @running_total + t.value AS CurrentBalance
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

NOTE: basically the idea is to reset the cumulative sum each time a reset is hit. Ideally I am trying to have an on update/insert trigger which will update the entire CurrentBalance column taking into account RESETs. This is a small table so I don't mind updating the whole table in exchange for something simple.

Just wrap the `@running_total + t.value` in an `IF(transactionType='RESET',0,[yourmath]) AS CurrentBalance` — Uueerdo, Aug 07 '15 at 19:42
A lot of people would advise against storing calculated values inside the database like that. — AdamMc331, Aug 07 '15 at 19:51
Agree! in fact I decided not to store the data, I will just create a View with this information. — shelbypereira, Aug 08 '15 at 08:23

score 3 · Accepted Answer · answered Aug 07 '15 at 19:42

All this requires is some simple conditional logic:

SELECT t.id, t.count,
       @running_total := if(transactionType = 'RESET', t.value,
                            @running_total + t.value
                           ) as CurrentBalance
FROM TABLE t JOIN
     (SELECT @running_total := 0) params
ORDER BY t.id;

MySQL calculation of cumulative sum with a reset condition

1 Answers1

Linked