SQL Query for Grouping the results based on sequence

Question

I have a table like this:

ID  Seq  Amt
1   1    500
1   2    500
1   3    500
1   5    500
2   10   600
2   11   600
3   1    700
3   3    700

I want to group the continuous sequence numbers into a single row like this:

ID  Start  End  TotalAmt
1   1      3    1500
1   5      5    500
2   10     11   1200
3   1      1    700
3   3      3    700

Please help to achieve this result.

Answer 1

WITH numbered AS (
  SELECT
    ID, Seq, Amt,
    SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq) - Seq
  FROM atable
)
SELECT
  ID,
  Start = MIN(Seq),
  [End] = MAX(Seq),
  TotalAmt = SUM(Amt)
FROM numbered
GROUP BY ID, SeqGroup
ORDER BY ID, Start
;

Answer 2

Well, there's perhaps a more elegant way to do this (something hints at me that there is), but here's an approach that will work if you're using a version of SQL Server that accepts common table expressions:

use Tempdb
go

create table [Test]
(
    [id] int not null,
    [Seq] int not null,
    [Amt] int not null
)

insert into [Test] values
(1, 1, 500),
(1, 2, 500),
(1, 3, 500),
(1, 5, 500),
(2, 10, 600),
(2, 11, 600),
(3, 1, 700),
(3, 3, 700)

;with
lower_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq - 1
    )
),
upper_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq + 1
    )
),
bounds as (
    select id, (select MAX(seq) from lower_bound where lower_bound.id = upper_bound.id and lower_bound.Seq <= upper_bound.Seq) as LBound, Seq as Ubound
      from upper_bound
)
select Test.id, LBound As [Start], UBound As [End], SUM(Amt) As TotalAmt
  from Test
  join bounds
    on Test.id = bounds.id
   and Test.Seq between bounds.LBound and bounds.Ubound
 group by Test.id, LBound, UBound

drop table [Test]