CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Kathir M
Kathir M

Reputation: 31

How to GROUP BY data in following method in SQL Server? Gaps and Islands

DECLARE @Table TABLE 
               (
                    Col_status VARCHAR(5),
                    Col_Location VARCHAR(5),     
                    Col_NUM INT
               )

INSERT INTO @Table (Col_status, Col_Location, Col_NUM)
VALUES ('ON', 'AAA', 1), ('ON', 'AAA', 2),
       ('OFF', 'AAA', 3), ('ON', 'BBB', 4),
       ('ON', 'BBB', 5), ('ON', 'CCC', 6), ('ON', 'BBB', 7),
       ('OFF', 'AAA', 9), ('OFF', 'AAA', 10);

SELECT *
FROM @Table

Output:

Col_status  Col_Location    Col_NUM
----------  ------------    -------
ON          AAA             1
ON          AAA             2
OFF         AAA             3
ON          BBB             4
ON          BBB             5
ON          CCC             6
ON          BBB             7
OFF         AAA             9
OFF         AAA            10

But I expect the following answer; I spent most of my time on this.

Output should be like this:

Col_status  Col_Location    Min(Col_NUM)    count(Col_NUM)
---------- -------------    -------------   --------------
ON          AAA             1               2
OFF         AAA             3               1
ON          BBB             4               2
ON          CCC             6               1
ON          BBB             7               1
OFF         AAA             9               2

Thanks and regards, Kathir M

Upvotes: 2

Views: 107

Answers (5)

DineshDB
DineshDB

Reputation: 6193

Try this:

DECLARE @Table TABLE (
 Col_status varchar(5),
      Col_Location varchar(5),     
      Col_NUM INT
)

INSERT INTO @Table (Col_status,Col_Location,Col_NUM)
      VALUES ('ON','AAA',1),('ON','AAA',2),('OFF','AAA',3),('ON','BBB',4),
('ON','BBB',5),('ON','CCC',6),('ON','BBB',7),('OFF','AAA',9),('OFF','AAA',10);

select A.Col_status,A.Col_Location
    ,MIN(A.col_num)[Min(Col_NUM)]
    ,COUNT(1)[count(Col_NUM)]
FROM
(
   SELECT t.*
    ,ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM) RN
   FROM @Table t
) A
group by A.Col_status,A.Col_Location, A.col_num - A.RN
ORDER BY min(A.col_num)

Upvotes: 1

Mazhar
Mazhar

Reputation: 3837

As mention by a previous answer this is a gaps and islands question.

So you need to create a query that will generate islands, so this example, here is one

;WITH cteX
AS(
   SELECT TOP 100 PERCENT
          t.Col_NUM
        , t.Col_Location
        , t.Col_status
        , RN = ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM) 
   FROM @Table t
   ORDER BY t.Col_NUM
)
SELECT
      X.Col_NUM
    , X.Col_Location
    , X.Col_status
    , X.RN
    , Island = X.Col_NUM - X.RN
FROM
    cteX X
ORDER BY
    X.Col_NUM - X.RN

Output shows the Islands

Islands

so using this query you can get the results you want 

;WITH cteX
AS(
   SELECT TOP 100 PERCENT
          t.Col_NUM
        , t.Col_Location
        , t.Col_status
        , RN = ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM) 
   FROM @Table t
   ORDER BY t.Col_NUM
)

SELECT
      X.Col_status
    , X.Col_Location
    , MIN_Col_Num   = MIN(X.Col_NUM)
    , COUNT_Col_NUM = COUNT(*)
FROM cteX X
GROUP BY
      X.Col_Location
    , X.Col_status
    , (X.Col_NUM - X.RN)
ORDER BY
   MIN_Col_Num,,(X.Col_NUM - X.RN)

Output

result

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10711

It is an island and gaps problem. It can be solved like this:

select t.Col_status,t.Col_Location, min(t.col_num), count(*)
from
(
   select t.*, row_number() over (partition by Col_status,Col_Location order by Col_NUM) group_rn
   from @Table t
) t
group by t.Col_status,t.Col_Location, t.col_num - t.group_rn

demo

the basic idea is in the t.col_num - t.group_rn, where the rows in the same sequence (having consecutive id) has the same result of this difference. Therefore, the t.col_num - t.group_rn allows you to isolate these sequences and do the group by correctly.

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37525

In this case, you need to group by two columns :) Try this query:

SELECT Col_status, Col_Location, MIN(Col_NUM), COUNT(Col_NUM)
FROM @Table
GROUP BY Col_status, Col_Location

Upvotes: 0

SQL_M
SQL_M

Reputation: 2475

Could you not just select the result into a temp table and then do this:

SELECT Col_Location, 
       Col_Status, 
       MIN (Col_Num), 
       COUNT(*) AS Count(Col_NUM) 
FROM #TMP
GROUP BY Col_Location, 
         Col_Status
ORDER BY 1, 2

Upvotes: 0

Related Questions