MySQL adding value from previous row

mysql cumulative-sum
nick picture nick · Oct 17, 2012 · Viewed 7.8k times · Source

I have a table with a single column. The column is like this:

1
2
3
4
5
...

I want to create a query that will display another column that will have the previous value added to it. So:

1 1 ( 0 + 1 )
2 3 ( 1 + 2 )
3 5 ( 2 + 3 )
4 7 ( 3 + 4 )
5 9 ( 4 + 5 )
9 14 (5 + 9)
45 54 ( 9 + 45)

How would I construct a query to accomplish that?

Essentially, I just want the difference between ROW[X], and ROW[X-1].

Answer

jaipster picture jaipster · Oct 17, 2012 
SELECT  a.val, (@runtot :=  a.val  + @runtot) AS rt, ( @runtot := a.val ) ne
FROM    Table1 a,(SELECT @runtot:=0) c

This seems to be working. I tried reinit the variable at each stage. Try it out.

SQLFiddle Demo

Related questions

Create a Cumulative Sum Column in MySQL

I have a table that looks like this: id count 1 100 2 50 3 10 I want to add a new column called cumulative_sum, so the table would look like this: id count cumulative_sum 1 100 100 2 50 150 3 10 160 Is there a MySQL update statement that can do …

Read More
Mysql calculation in select statement

I have been doing my office work in Excel.and my records have become too much and want to use mysql.i have a view from db it has the columns "date,stockdelivered,sales" i want to add another calculated …

Read More
MySQL cumulative sum grouped by date

I know there have been a few posts related to this, but my case is a little bit different and I wanted to get some help on this. I need to pull some data out of the database that is …

Read More