Results 1 to 12 of 12
  1. #1
    Join Date
    May 2007
    Posts
    6

    Unanswered: 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:

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    6
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2007
    Posts
    6
    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

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    May 2007
    Posts
    6
    Thanks Peter,

    I will test your script, too.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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;

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •