Reputation: 31
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
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
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
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
Upvotes: 1
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
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
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
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