MySQL select "accumulated" column

Question

I'm not sure what to call this besides an "accumulated" column.

I have a MySQL table with a column that looks like

+---+
|val|
+---+
| 1 |
| 4 |
| 6 |
| 3 |
| 2 |
| 5 |
+---+

I want to do a query so that I get this column along with another column which is the sum of all the rows from this column so far. In other words, the select would yield

+---+----+
|val| sum|
+---+----+
| 1 |  1 |
| 4 |  5 |
| 6 | 11 |
| 3 | 14 |
| 2 | 16 |
| 5 | 21 |
+---+----+

Does anyone know how I would do this, and whether you can do this in MySQL?

Answer 1

I don't know how\if you would do that with one select but I think you could select the elements then have a cursor 'accumulate' on the second pass.

Answer 2

What about

set @running_sum=0
select val, @running_sum:=@running_sum + val from your_table;

I'm new to mysql so take what I say with a grain of salt.

What you want is called a running sum. There are some google results.

Answer 3

I would use a cursor:

>>> import MySQLdb
>>> db = MySQLdb.connect("localhost","username","password","test")
>>> cur = db.cursor()
>>> cur.execute("select myval from mytable")
>>> rs = cur.fetchall()
>>> accumulator = 0
>>> for r, in rs:
...   accumulator += r
...   print r, accumulator
...
1 1
4 5
6 11
3 14
2 16
5 21

Answer 4

Another variation (single line) can be

SELECT val, @sm := @sm + val AS sum
  FROM myTable, (SELECT @sm := 0) r;

If you don't want to use variables you can use nested select however note that this query has higher complexity (lower performance) and you must set and anchor for sorting, in my example I used _rowid but any other field can do the job

  SELECT x1.val, (SELECT sum(val)
                    FROM myTable
                   WHERE id <= x1._rowid)
                    AS sum
    FROM myTable AS x1
ORDER BY _rowid;