sql - MySQL SELECT function to sum current data

Question

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

Answer 1

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
;