Accumulation of row values in a specific column

[joacker]

Hi,

i have a table A:
ID | VALUE
1 | 2
2 | 4
4 | 5
...

Now i want to create a select statement that adds the value of row n to row n+1 and that produce the following output:

ID | VALUE
1 | 2
2 | 6 (= 2 + 4)
3 | 11 (= 5 + 6)
...

I am sure there is an easy way to handle this.
Thx for any help.

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=blue]Value[/color] [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] @Test [COLOR=blue]VALUES[/color] (1, 2)
[COLOR=blue]INSERT[/color] @Test [COLOR=blue]VALUES[/color] (2, 4)
[COLOR=blue]INSERT[/color] @Test [COLOR=blue]VALUES[/color] (4, 5)


[COLOR=blue]SELECT[/color] YourTable.Id,
       SUM([COLOR=#FF00FF]ISNULL[/color](Tbl1.Value,0)) [COLOR=blue]AS[/color] aaa
[COLOR=blue]FROM[/color] @Test YourTable
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Test  Tbl1 [COLOR=blue]ON[/color] YourTable.Id >= Tbl1.Id
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] YourTable.Id

not tested well.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Geoka]

Small correction in the code for fixing the ID field. It should be like running sequence number.

DECLARE @Test TABLE (Id int, Value int)
INSERT @Test VALUES (1, 2)
INSERT @Test VALUES (2, 4)
INSERT @Test VALUES (4, 5)


SELECT [b]count(ISNULL(Tbl1.id,0)) AS id[/b],
       SUM(ISNULL(Tbl1.Value,0)) AS aaa,
count(ISNULL(Tbl1.id,0)) AS bbb
FROM @Test YourTable
LEFT JOIN @Test  Tbl1 ON YourTable.Id >= Tbl1.Id
GROUP BY YourTable.Id

 

[joacker]

thank you guys.