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
;

Answer 2

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