group several rows in a SQL query

GNiewerth · #1 (**permalink**) 06-14-07, 08:34

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:

Code:

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,
Guido

r937 · #2 (**permalink**) 06-14-07, 10:29

a few sql commands? how about just one?

Code:

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 )

GNiewerth · #3 (**permalink**) 06-15-07, 11:36

Thanks for your response, it´s awesome, but I don´t 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 isn´t fixed, either, it depends on user input.

Thank you anyway,
Guido

r937 · #4 (**permalink**) 06-15-07, 12:18

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

blindman · #5 (**permalink**) 06-15-07, 13:00

Asking the question you want anwsered? Hmm.....a radical concept.

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

Code:

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.

GNiewerth · #6 (**permalink**) 06-15-07, 14:01

Quote:

Originally Posted by r937

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:

Quote:

Originally Posted by GNiewerth

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. I´ll try your solution, many thanks.

Guido

Peter.Vanroose · #7 (**permalink**) 06-17-07, 09:34

Maybe this does what you want:

Code:

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

Peter.Vanroose · #8 (**permalink**) 06-17-07, 10:03

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:

Code:

WITH t(value, nr) AS
(SELECT value, id/17 AS nr
 FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr

Or possibly even, if your SQL allows grouping by expression:

Code:

SELECT SUM(value)
FROM myTable
GROUP BY id/17

or possibly (in case your SQL doesn't perform integer division):

Code:

SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)

GNiewerth · #9 (**permalink**) 06-18-07, 10:27

Thanks Peter,

I will test your script, too.

mike_bike_kite · #10 (**permalink**) 06-19-07, 11:54

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

Code:

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;