SQL Count Grouping by a sequence of numbers

Question

i have a SQL table like this:

id      pNum
-----   --------
100     12
100     13
100     15
100     16
100     17
200     18
200     19
300     20
300     21
300     25

and i want to group by the id and the pNum sequences, and count the number of rows. having a result like this.

id      res
-----   --------
100     2
100     3
200     2
300     2
300     1

any idea on how to do it?

Answer 1

Using the solution from this question:

declare @table table
(
    id int
    , pnum int
)


insert into @table
values (100,    12)
, (100,     13)
, (100,     15)
, (100,     16)
, (100,     17)
, (200,     18)
, (200,     19)
, (300,     20)
, (300,     21)
, (300,     25)

;WITH numbered AS (
  SELECT
    ID, pnum,
    SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY pnum) - pnum
  FROM @table
)
SELECT
  ID,
  COUNT(*) AS res
FROM numbered
GROUP BY ID, SeqGroup
ORDER BY id, MIN(pnum)

Answer 2

If your DBMS supports window functions (e.g. SQL Server 2005+)

SELECT id,
       count(*) AS res
FROM   (SELECT *,
               [pNum] - ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [pNum]) AS Grp
        FROM   YourTable) T
GROUP  BY id,
          Grp 

SQL Fiddle