group several rows in a SQL query

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

id value
1 1
2 3
3 5
4 7
5 9

The result I expect from the query are 3 rows: [9, 15,21]:
1st row: 1+3+5 = 9
2nd row: 3+5+7 = 15
3rd row: 5+7+9 = 21

Help is appreciated,
Guidoa few sql commands? how about just one? :)select this.value + prev.value + next.value as result
from daTable as this
inner
join daTable as prev
on prev.id =
( select max(id)
from daTable
where id < this.id )
inner
join daTable as next
on next.id =
( select min(id)
from daTable
where id > this.id )|||Thanks for your response, its awesome, but I dont think it fits my needs. I gave an example with just three consecutive rows accumulated, in real life it will be hundreds of rows.
The number of consecutive rows isnt fixed, either, it depends on user input.

Thank you anyway,
Guido|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

i'm done in this thread|||Asking the question you want anwsered? Hmm....a radical concept.

This code will work provided your IDs are consecutively numbered with no gaps:

declare @.RangeLength
set @.RangeLength = 3

select t1.id,
sum(t2.value)
from [YourTable] t1
inner join [YourTable] t2 on t2.id between t1.id-@.RangeLength+1 and t1.id
group by t1.id

If your IDs are not uniformly sequential, then things get more complex, and might best be solved using a temporary table.|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

I did... not as clearly as I should have, but I did:

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

@.blindman:
Yes, my IDs are consecutively numbered, without gaps. Ill try your solution, many thanks.

Guido|||Maybe this does what you want:WITH t(value, nr) AS
(SELECT value, (ROW_NUMBER() OVER (ORDER BY id) - 1)/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr(Just replace 17 by the number of rows you want to group.)|||Alternatively, if your SQL doesn't have ROW_NUMBER(), and/or if your "id" column just contains all numbers 1, 2, 3 etc. (which means they already contain the ROW_NUMBER()s), the following gives the same result:WITH t(value, nr) AS
(SELECT value, id/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nrOr possibly even, if your SQL allows grouping by expression:SELECT SUM(value)
FROM myTable
GROUP BY id/17or possibly (in case your SQL doesn't perform integer division):SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)|||Thanks Peter,

I will test your script, too.|||Guido

The following seems to work fine and may be easier to understand.
Again the id's need to be sequential but I believe you said they were.

Mike

select t1.val + t2.val + t3.val as vals
from my_table t1,
my_table t2,
my_table t3
where t1.id < t2.id
and t2.id < t3.id
and t3.id < t1.id + 3;|||Easier to understand than a single join? I don't think so.
Plus, you need to read GNiewerth's second post. Your solution is not scalable.|||you need to read GNiewerth's second post. Your solution is not scalable.
You're right - I didn't see that requirement - please ignore my code then!
Mike