CODEWITHSUNDEEP
mysql
Marquis Wang
Marquis Wang

Reputation: 11108

MySQL select "accumulated" column

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?

Upvotes: 8

Views: 6782

Answers (4)

Nir
Nir

Reputation: 51

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;

Upvotes: 5

mechanical_meat
mechanical_meat

Reputation: 169304

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

Upvotes: 0

seth
seth

Reputation: 37267

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.

Upvotes: 9

javamonkey79
javamonkey79

Reputation: 17775

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.

Upvotes: 0

Related Questions