Cumulative Sum for each unique ID in every month

Question

I am trying to calculate the cumulative profit from all clients and I find a very good reference from here. However, my table consist of more than 1 client that should have their own cumulative profit. Below is the table 'client' that I already have.

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |            |
| 2017 |   2   | 123 |  -200  |            |
| 2017 |   3   | 123 |  500   |            |
| 2017 |   1   | 456 |  500   |            |
| 2017 |   2   | 456 |  100   |            |
| 2017 |   3   | 456 |  200   |            |

If I am using a sql code like this:

SET @csum := 0;
UPDATE client
SET cumulative = (@csum := @csum + profit);

The result I get is like this:

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |    1000    |
| 2017 |   2   | 123 |  -200  |    800     |
| 2017 |   3   | 123 |  500   |    1300    |
| 2017 |   1   | 456 |  500   |    1800    |
| 2017 |   2   | 456 |  100   |    1900    |
| 2017 |   3   | 456 |  200   |    2100    |

What I expect to get is like this:

| Year | Month | id  | profit | cumulative |
| 2017 |   1   | 123 |  1000  |    1000    |
| 2017 |   2   | 123 |  -200  |    800     |
| 2017 |   3   | 123 |  500   |    1300    |
| 2017 |   1   | 456 |  500   |    500     |
| 2017 |   2   | 456 |  100   |    600     |
| 2017 |   3   | 456 |  200   |    800     |

I am trying to group it by Year, Month, and id as well, but it doesn't work. Basically, I want the cumulative sum for every unique client in each month. Do you have any idea how to solve this? Thanks in advance.

reset the sum when id changes. – Salman A Jan 19 '18 at 11:12 — Salman A, Jan 19 '18 at 11:12

Radim Bača · Answer 1 · 2018-01-19T10:55:57.140

1

I would avoid local variables since the result can be sometimes different then expected and also set based approach can be optimized better by the DBMS. Use subquery or self-join instead:

SELECT c1.*,
       (SELECT SUM(c2.profit)
        FROM client c2
        WHERE (c2.year < c1.year or 
              (c2.year = c1.year and c2.month <= c1.month)) and 
              c2.id = c1.id
       ) AS cumulative_sum
FROM TABLE client c1

therefore in update it can like this

UPDATE client
JOIN
(
   SELECT c1.id, c1.year, c1.month,
       (SELECT SUM(c2.profit)
        FROM client c2
        WHERE (c2.year < c1.year or 
              (c2.year = c1.year and c2.month <= c1.month)) and 
              c2.id = c1.id
       ) AS cumulative_sum
   FROM client c1
) t ON client.id = t.id and
     client.year = t.year and
     client.month = t.month
SET cumulative = t.cumulative_sum

sqlfiddle demo (Thanks @JohnWoo for the data)

edited Jan 19 '18 at 10:55

answered Jan 19 '18 at 09:26

Radim Bača

10,646
1
19
33

`c2.year <= c1.year and c2.month <= c1.month and..` not gonna work as expected. Need to convert to date – Pரதீப் Jan 19 '18 at 09:28
@RadimBača I get this error message if using the update : #1093 - Table 'c1' is specified twice, both as a target for 'UPDATE' and as a separate source for data – Reza Satnaz Jan 19 '18 at 09:51
@RadimBača Thanks for your help – Reza Satnaz Jan 22 '18 at 02:21

Mike · Accepted Answer · 2018-01-19T10:03:34.407

Local variables works correctly only with ORDER BY in query.

SET @csum := 0, @id:=NULL;
UPDATE client
   SET cumulative = (@csum := if(id=@id,@csum,0) + profit), id=(@id:=id)
ORDER BY id, year, month;

Example on sqlfiddle.com

Or more shortly: ... SET cumulative = (@csum := if(id=@id, @csum, 0*(@id:=id) ) + profit). This comare stored ID with current ID, return stored SUM if ID's same, and return 0 (and store new ID) if ID's different.

score 1 · Answer 3 · answered Jan 19 '18 at 11:42

You can do this using variables, but you need to be very careful. When using variables, you want all the operations to be in a single statement -- because MySQL does not guarantee the order of evaluation of statements:

SET @csum := 0;
SET @id := -1;

UPDATE client c
    SET cumulative = (CASE WHEN @id = id
                           THEN @csum := @csum + profit
                           WHEN @id := id
                           THEN @csum := profit
                           ELSE @csum := profit
                      END)
    ORDER BY id, year, month;

Cumulative Sum for each unique ID in every month

3 Answers3