February 17, 2016 at 7:43 am
Hi,
I have a aggregate query that calculates pass/ fail percentages. I want to be able to run it based on different groupings. Is there any easy way to do this other than writing a separate query for each Grouping level required? In the example below, I want to be able to choose between Grouping on Level1 or Level2
CREATE TABLE dbo.Table_1
(
ID int NOT NULL IDENTITY (1, 1),
Level1 varchar(50) NOT NULL,
Level2 varchar(50) NOT NULL,
Result varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
The query would then be:
SELECT Level1
, COUNT(CASE WHEN Result = 'PASS' THEN 1 ELSE 0 END) AS Passes
, COUNT(CASE WHEN Result = 'FAIL' THEN 1 ELSE 0 END) AS Fails
FROM Table_1
GROUP BY Level1
or
SELECT Level2
, COUNT(CASE WHEN Result = 'PASS' THEN 1 ELSE 0 END) AS Passes
, COUNT(CASE WHEN Result = 'FAIL' THEN 1 ELSE 0 END) AS Fails
FROM Table_1
GROUP BY Level2
February 17, 2016 at 8:32 am
Use GROUPING SETS. Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
SELECT Level1, Level2
, COUNT(CASE WHEN Result = 'PASS' THEN 1 ELSE 0 END) AS Passes
, COUNT(CASE WHEN Result = 'FAIL' THEN 1 ELSE 0 END) AS Fails
FROM Table_1
GROUP BY GROUPING SETS ((Level1), (Level2))
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 17, 2016 at 10:34 am
That's exactly what I wanted to do. Very clever (I hadn't come across Grouping Sets before).
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply