CODEWITHSUNDEEP
t-sql
Don Hessey
Don Hessey

Reputation: 67

Group ever 4 rows with over lap

Sorry for the vague title but I'm not sure how to phrase the question. What I want to do is sum a column in groups if every 4 records, but the groupings will overlap

 Game       Strikeouts
    1.                 25
    2.                 10
    3.                 10
    4.                 11
    5.                 16

Show the first group would be games 1-4 and sum would be 56, second group would be 2-5 and sun would 47, and so on all the way down to the last record.

Upvotes: 2

Views: 38

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Another option

Select Game
      ,Strikeouts = sum(Strikeouts) over (Order By Game ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) 
 From  YourTable
 Order By Game

Returns

Game    Strikeouts
1       56
2       47
3       37
4       27
5       16

Upvotes: 4

tgr
tgr

Reputation: 254

I simply added the values of the next 3 rows to the actual row:

CREATE TABLE #TEST (
  Game int
 ,Strikeouts int
)
INSERT INTO #TEST VALUES 
(1,25),(2,10),(3,10),(4,11),(5,16)

-- act. row + 3 following rows
SELECT Game, Strikeouts + LEAD(Strikeouts,1,0) OVER (ORDER BY Game) + LEAD(Strikeouts,2,0) OVER (ORDER BY Game) + LEAD(Strikeouts,3,0) OVER (ORDER BY Game) as Strikeouts
FROM #TEST

Output:

Game |Strikeouts
-----+-----------
  1  |        56
  2  |        47
  3  |        37
  4  |        27
  5  |        16

Lag was introduced with SQL Server 2012

Upvotes: 3

Related Questions