troubleshooting Question

TSQL syntax needed to count values based on grouping

Avatar of crompnk
crompnkFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
3 Comments1 Solution74 ViewsLast Modified:
Hi,

I need to count the number of occurrences of Zones A, B and C in the temp table as seen in the code below.

The tsql first groups instances where the same [NAME] and [Type] column values occur consecutively with a factor of 3. Then I need to count the number of A,B & C zones, the expected outcome is:

--Required outcome

--NAME	Type	Count	ZoneA	ZoneB	ZoneC
------------------------------------------------
--Name1	Type1	3	2	1	0
--Name1	Type2	3	1	0	2
--Name2	Type1	3	1	2	0

DECLARE @TempTable TABLE
([Id] INT
,[Name] VARCHAR(20)
,[Type] VARCHAR(10)
,[Zone] VARCHAR(10)
)

INSERT INTO @TempTable
VALUES(1,'Name1','Type2','A')
,(2,'Name1','Type1','B')
,(3,'Name1','Type1','A')
,(4,'Name1','Type1','A')
,(5,'Name1','Type2','C')
,(6,'Name1','Type2','C')
,(7,'Name1','Type2','A')
,(8,'Name2','Type1','A')
,(9,'Name2','Type1','B')
,(10,'Name2','Type1','B')

--SELECT * FROM @TempTable

;with c as (
    select 
       [NAME], [Type], [Zone],
       row_number() over (partition by [NAME] order by [NAME], [Id] ASC) 
       - row_number() over (partition by [NAME], [Type] order by [NAME], [Id] ASC) grp
from (SELECT TOP 100 PERCENT *
FROM @TempTable
ORDER BY [NAME], [Id] ASC
)T1
)

SELECT [NAME],[Type],count([Type]) [Count]
FROM c
GROUP BY [NAME],[Type], grp
HAVING count([Type]) = 3

Thank you
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros