CODEWITHSUNDEEP
mysqlsqlselect
rnunes
rnunes

Reputation: 2835

MySQL SELECT function to sum current data

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

Upvotes: 2

Views: 1586

Answers (3)

Quassnoi
Quassnoi

Reputation: 425331

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
;

Upvotes: 7

Yurie
Yurie

Reputation: 899

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)

Upvotes: 0

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

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

Upvotes: 3

Related Questions