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?

what res does represent in the grouped table – Saddam Abu Ghaida Feb 07 '13 at 15:46 — Saddam Abu Ghaida, Feb 07 '13 at 15:46
Micorsoft SQL ----------------- – Thorgrim the Dwarf Feb 07 '13 at 15:48 — Thorgrim the Dwarf, Feb 07 '13 at 15:48
Oh, you're grouping "res" as continuous sequences, right? – Nick Vaccaro Feb 07 '13 at 15:48 — Nick Vaccaro, Feb 07 '13 at 15:48
res is the count of id grouped by sequences of pnum – Thorgrim the Dwarf Feb 07 '13 at 15:48 — Thorgrim the Dwarf, Feb 07 '13 at 15:48
nick is right, grouping by continuous sequences – Thorgrim the Dwarf Feb 07 '13 at 15:49 — Thorgrim the Dwarf, Feb 07 '13 at 15:49
smells of recursion; and of homework ... – Bogdan Gavril MSFT Feb 07 '13 at 15:50 — Bogdan Gavril MSFT, Feb 07 '13 at 15:50

Martin Smith · Accepted Answer · 2013-02-07T15:49:54.063

4

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

edited Feb 07 '13 at 15:49

answered Feb 07 '13 at 15:44

Martin Smith

438,706
87
741
845

2

@user2051336 - This type of requirement is often known as finding "gaps and islands". The approach in my answer is attributed to Itzik Ben Gan AFAIK. What version of SQL Server are you on? – Martin Smith Feb 07 '13 at 15:59
@MartinSmith - This one might also be useful for MS SQL server http://stackoverflow.com/a/4324654/247184 – VoodooChild Sep 04 '14 at 20:25

score 2 · Answer 2 · edited May 23 '17 at 11:51

2

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)

edited May 23 '17 at 11:51

Community

1
1

answered Feb 07 '13 at 15:52

Nick Vaccaro

5,428
6
38
60

1

Nice trick with the `row_number() - pnum`. What does this assignement thing `SeqGroup = ` do? – Feb 07 '13 at 15:59
@a_horse_with_no_name - As I suspect you are probably aware it is a SQL Server specific way of aliasing columns. – Martin Smith Feb 07 '13 at 16:01
@MartinSmith: not really. Is there a difference to the `as foo` standard syntax? – Feb 07 '13 at 16:03
@a_horse_with_no_name - Not really. Some people (such as Aaron Bertrand) prefer it as it is easier to lime up the column aliases. [Bad Habits to Kick : Using AS instead of = for column aliases](https://sqlblog.org/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases) – Martin Smith Feb 07 '13 at 16:04

SQL Count Grouping by a sequence of numbers

2 Answers2

Linked