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?
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
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:
JOIN (SELECT @running_total := 0) r is a cross join, and allows for variable declaration without requiring a separate SET command. r, is required by MySQL for any subquery/derived table/inline view Caveats:
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)
SELECT in the JOIN (SELECT @running_total := 0) part of the variables example.
If performance is an issue, you could use a MySQL variable:
set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;
Alternatively, you could remove the cumulative_sum column and calculate it on each query:
set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;
This calculates the running sum in a running way :)
SET.
Name or similar, and then do a cumulative sum only for records with the same name
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 │
└─────┴──────┴────────────────┘
INSERT INTO table_name(id, cnt, cumulative_sum) SELECT ... FROM ... or CREATE TABLE table_name AS SELECT ... FROM ...UPDATE t
SET cumulative_sum = (
SELECT SUM(x.count)
FROM t x
WHERE x.id <= t.id
)
select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;
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
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.
SELECT ...., (SELECT .... FROM table2 WHERE table2.id = table1.id ) FROM table1 what you have is a window query..
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
