I have a query that return something like this:
| ID | Val |
| 0 | 10 |
| 1 | 20 |
| 2 | 30 |
But instead of that, I want something like this:
| ID | Val | Sum |
| 0 | 10 | 10 |
| 1 | 20 | 30 |
| 2 | 30 | 60 |
Is that a way to do it on the query (I'm using MySQL)?
Tks
This is called cumulative sum.
In Oracle and PostgreSQL, it is calculated using a window function:
SELECT id, val, SUM() OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM mytable
However, MySQL does not support it.
In MySQL, you can calculate it using session variables:
SET @s = 0;
SELECT id, val, @s := @s + val
FROM mytable
ORDER BY
id
;
or in a pure set-based but less efficient way:
SELECT t1.id, t1.val, SUM(t2.val)
FROM mytable t1
JOIN mytable t2
ON t2.id <= t1.id
GROUP BY
t1.id
;
Would something like this work for your purposes? (Warning, potentially really darned slow with the subselect).
SELECT t1.id, t1.val, (SELECT SUM(val) FROM table AS t2 WHERE t2.id <= t1.id) 'sum'
FROM table AS t1
ORDER BY id ASC
Assuming the table name is t, you can use a query like this:
select t.id, t.val, sum(t2.val) Sum
from t, t t2
where t2.id <= t.id
group by t.id, t.val
(tested in Oracle)
