[mysql] Create a Cumulative Sum Column in MySQL

I have a table that looks like this:

id   count
1    100
2    50
3    10

I want to add a new column called cumulative_sum, so the table would look like this:

id   count  cumulative_sum
1    100    100
2    50     150
3    10     160

Is there a MySQL update statement that can do this easily? What's the best way to accomplish this?

UPDATE t
SET cumulative_sum = (
 SELECT SUM(x.count)
 FROM t x
 WHERE x.id <= t.id
)

select id,count,sum(count)over(order by count desc) as cumulative_sum from tableName;

I have used the sum aggregate function on the count column and then used the over clause. It sums up each one of the rows individually. The first row is just going to be 100. The second row is going to be 100+50. The third row is 100+50+10 and so forth. So basically every row is the sum of it and all the previous rows and the very last one is the sum of all the rows. So the way to look at this is each row is the sum of the amount where the ID is less than or equal to itself.

MySQL 8.0/MariaDB supports windowed SUM(col) OVER():

SELECT *, SUM(cnt) OVER(ORDER BY id) AS cumulative_sum
FROM tab;

Output:

+-----------------------------+
¦ id  ¦ cnt  ¦ cumulative_sum ¦
+-----+------+----------------¦
¦  1  ¦ 100  ¦            100 ¦
¦  2  ¦  50  ¦            150 ¦
¦  3  ¦  10  ¦            160 ¦
+-----------------------------+

db<>fiddle

select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;

Using a correlated query:

  SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

Using MySQL variables:

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

Note:

• The JOIN (SELECT @running_total := 0) r is a cross join, and allows for variable declaration without requiring a separate SET command.
• The table alias, r, is required by MySQL for any subquery/derived table/inline view

Caveats:

• MySQL specific; not portable to other databases
• The ORDER BY is important; it ensures the order matches the OP and can have larger implications for more complicated variable usage (IE: psuedo ROW_NUMBER/RANK functionality, which MySQL lacks)

You could also create a trigger that will calculate the sum before each insert

delimiter |

CREATE TRIGGER calCumluativeSum  BEFORE INSERT ON someTable
  FOR EACH ROW BEGIN

  SET cumulative_sum = (
     SELECT SUM(x.count)
        FROM someTable x
        WHERE x.id <= NEW.id
    )

    set  NEW.cumulative_sum = cumulative_sum;
  END;
|

I have not tested this

  select t1.id, t1.count, SUM(t2.count) cumulative_sum
    from table t1 
        join table t2 on t1.id >= t2.id
    group by t1.id, t1.count

Step by step:

1- Given the following table:

select *
from table t1 
order by t1.id;

id  | count
 1  |  11
 2  |  12   
 3  |  13

2 - Get information by groups

select *
from table t1 
    join table t2 on t1.id >= t2.id
order by t1.id, t2.id;

id  | count | id | count
 1  | 11    | 1  |  11

 2  | 12    | 1  |  11
 2  | 12    | 2  |  12

 3  | 13    | 1  |  11
 3  | 13    | 2  |  12
 3  | 13    | 3  |  13

3- Step 3: Sum all count by t1.id group

select t1.id, t1.count, SUM(t2.count) cumulative_sum
from table t1 
    join table t2 on t1.id >= t2.id
group by t1.id, t1.count;


id  | count | cumulative_sum
 1  |  11   |    11
 2  |  12   |    23
 3  |  13   |    36

Sample query

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(date) AS d,
       COUNT(*) AS c
    FROM  orders
    WHERE  hasPaid > 0
    GROUP  BY d
    ORDER  BY d) AS q1