CODEWITHSUNDEEP
sqlsql-servergroup-bycommon-table-expression
Felipe Goyes
Felipe Goyes

Reputation: 11

SQL group sequence ids and count them (sqlServer)

Easy question: Following single table with IDs

    IDS
    5
    6
    7
    8
    13
    14

What I want

    MinID   MaxID   Count
    5       8       4
    13      14      2

Any Idea? CTE are my pesonal preferences

Upvotes: 0

Views: 239

Answers (5)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can also try the below query. It groups them together based on the range.

This will help even when your values are not sequential.

select min(id) as MinID, max(id) as MaxID, count(*) as cnt
from (select id,case when id between 1 and 9 then '1-9'
                     when id between 10 and 19 then '10-19'
                   --when id between 20 and 29 then '20-29' & so on
                end as Grp from your_table) g
group by Grp

CHECK DEMO HERE

You can alter the case based on your logic for spiting the range values i.e.,1-9 or 1-10

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

A simple method uses row_number() and group by:

select min(id), max(id), count(*)
from (select t.*,
             row_number() over (order by id) as seqnum
      from t
     ) t
group by (id - seqnum)
order by min(id);

That is, if the difference between a sequence of numbers and id is constant when the id values are sequential.

Upvotes: 2

deHaar
deHaar

Reputation: 18568

This example uses variables for minimum and maximum id:

DECLARE @minID INTEGER,
        @maxID INTEGER;

SET @minID = 13;
SET @maxID = 14;

SELECT
    @maxID AS MaxID,
    @minID AS MinID,
    COUNT(*) AS "Count"
FROM
    #ids
WHERE
    id <= @maxID
    AND
    id >= @minID

and it outputs

MaxID   MinID   Count
14      13      2

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use lag() find the grouping & do aggregation : 

select min(ids) as minid, max(ids) as maxid, count(*) as cnt
from (select t.*, 
             sum(case when ids - prev_ids = 1 then 0 else 1 end) over (order by ids) as grp
      from (select t.*, lag(ids) over (order by ids) as prev_ids
            from table t
           ) t
     ) t
group by grp;

Upvotes: 0

Digvijay S
Digvijay S

Reputation: 2705

I assume you want to group by based on number of digit. 

select len(id) ln , min(id) , max(id) , count (*) from tab1
group by len(id) ;

Demo

Upvotes: 0

Related Questions