asked
Summation of Adjacent Rows
declare @Yourtable table ([Row] int, [status] char(1),Score int) insert into @Yourtable select 1,'H', 5 insert into @Yourtable select 2,'L', 4 insert into @Yourtable select 3,'H', 1 insert into @Yourtable select 4,'H', 5 insert into @Yourtable select 5,'L', 2 insert into @Yourtable select 6,'L', 3 insert into @Yourtable select 7,'H', 7 insert into @Yourtable select 8,'L' ,4 insert into @Yourtable select 9,'L', 3 select * from @Yourtable Data From above table: 1 H 5 2 L 4 **3 H 1 4 H 5** 5 L 2 6 L 3 7 H 7 8 L 4 9 L 3 Desired Result: H 5 L 4 **H 6** L 5 H 7 L 7
Variation of the 'islands' problem (part of 'Gaps and Islands') ;with cts_islands as ( select Row , status , Score, row_number()over(partition by status order by row) - row as grp from @Yourtable ) select status, sum(score) from cts_islands group by status, grp order by min(row)
15 People are following this question.
