Sum every X rows with no common group by parent Trying to sum every X rows with no common group by parent

declare @temp table(Row_ID int, amt DECIMAL(18,2))
insert into @temp values(1, 20)
insert into @temp values(2, 24)
insert into @temp values(3, 20)
insert into @temp values(4, 15)
insert into @temp values(5, 15)


If X rows is 2 then desired result is to sum ever 2 rows and place total next to the rows. So first 2 rows total to 44 , next 2 rows to 35 , 1 remaining so total  15

Row_ID  amt     tot
1       20.00    44.00
2       24.00    44.00
3       20.00    35.00
4       15.00    35.00
5       15.00    15.00


If X rows is 3 then desired result

Row_ID  amt     tot
1       20.00    64.00
2       24.00    64.00
3       20.00    64.00
4       15.00    30.00
5       15.00    30.00


If X rows is 1 then desired result

Row_ID  amt     tot
1       20.00    20.00
2       24.00    24.00
3       20.00    20.00
4       15.00    15.00
5       15.00    15.00
One way is to divide the rows into groups/partitions - using simple math (integer division) and group over the partitions. I assume that the Row_ID values are UNIQUE.

If - unlikely but if - there are no gaps in the Row_ID values and they start from 1, then we could use the simple:

select row_id, amt,
       sum = sum(amt) over (partition by (row_id-1)/@GroupSize)
from t ;


But since this is unlikely - the values probably come from an IDENTITY column or a SEQUENCE, which don't guarantee gapless sequences - we have to create a gapless sequence, with ROW_NUMBER():

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / @GroupSize
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;


Tested in dbfiddle.uk:




create table t 
(Row_ID int, amt DECIMAL(18,2)
) ;
GO



✓






insert into t values
  (1, 20),
  (2, 24),
  (3, 20),
  (4, 15),
  (5, 15) ;
GO



5 rows affected






with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 2
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO



row_id | amt   | sum  
-----: | :---- | :----
     1 | 20.00 | 44.00
     2 | 24.00 | 44.00
     3 | 20.00 | 35.00
     4 | 15.00 | 35.00
     5 | 15.00 | 15.00






with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 3
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO



row_id | amt   | sum  
-----: | :---- | :----
     1 | 20.00 | 64.00
     2 | 24.00 | 64.00
     3 | 20.00 | 64.00
     4 | 15.00 | 30.00
     5 | 15.00 | 30.00



In order to make the number of rows parametrisable, you just need to replace the divisor in the grp expression with a parameter/variable reference:

grp = (row_number() over (order by row_id) - 1) / @GroupSize


and, consequently, pass the parameter value when you invoke the query.