4

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].

nick
  • 2,743
  • 4
  • 31
  • 39
  • nick i just deleted the answer because I didn't tested it and it shows different result from what you want. can you explain a little bit more or provide much better example. This is my previous code `SELECT a.val, (@runtot := @runtot + a.val) AS rt FROM table1 a,(SELECT @runtot:=0) c`. Thanks. – John Woo Oct 17 '12 at 04:44
  • i just want to get the previous row's value and add it to the current row's value and display it in a new column. thats it – nick Oct 17 '12 at 04:45
  • i updated the example in my question – nick Oct 17 '12 at 04:47

2 Answers2

15
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

Community
  • 1
  • 1
jaipster
  • 11,967
  • 2
  • 21
  • 24
  • 2
    I actually came up with that just now. The only thing i cant do is return just the `rt`, not the `ne`.. this is a subquery – nick Oct 17 '12 at 05:14
  • 2
    yea true. Thanx :) I am happy to get an upvote for the answer my first reputation :) – jaipster Oct 17 '12 at 05:17
-1

TRY 

SELECT  a.int, (@runtot :=  a.int  + a.int - 1) AS rt
FROM    test a,(SELECT @runtot:=0) c

ie 

1 1 ( 0 + 1 )
2 3 ( 1 + 2 )
3 5 ( 2 + 3 )
4 7 ( 3 + 4 )
5 9 ( 4 + 5 )

this will result the output you shown

simply-put
  • 1,068
  • 1
  • 11
  • 20
  • although this accomplishes what's in the example, in my actual scenario the numbers may skip which would miscalculate. that's why i need to record the last row's value. i should have been more explicit about that sorry – nick Oct 17 '12 at 04:41