tsql - Group by occurrence of row -

have table below have take sum of col2 based on group by. belongs chain, if chain breaks sum limited sequence.

declare @tabvar table (   id int identity(1,1), col1 varchar(20), col2 int ) insert @tabvar values ('a',2),('a',3),('b',4),('b',2),('a',6),('a',3),('b',3)  select * @tabvar   expected output:   col1     sum(col2)            5   b          6            9   b          3 

i have tried ranking functions ranking done using order accumulate total of col1

you can use row_number() partition by , group by achieve this.

whenever chain breaks, id - row_number()over(partition col1 order id) have different value same col1 value. can use along col1 group data , sum. this

sample data

declare @tabvar table (   id int identity(1,1), col1 varchar(20), col2 int ) insert @tabvar values ('a',2),('a',3),('b',4),('b',2),('a',6),('a',3),('b',3) 

query

select col1,sum(col2) sumcol ( select id - row_number()over(partition col1 order id) grpcol,col1,col2,id @tabvar )t group grpcol,col1 order max(id) 

output

col1 sumcol   5 b   6   9 b   3 

edit

incase ids not consecutive in live environment, can use this

select col1,sum(col2) sumcol ( select row_number()over(order id)  - row_number()over(partition col1 order id) grpcol, col1,col2,id @tabvar )t group grpcol,col1 order max(id)