SQL query to obtain totals and subtotals

Question

The query works fine but I'm just intrigued to know if there is a cleaner solution. The data are results from a survey and I'm obtaining the counts per question within a zone and an overall zone count.

With Cte1 (ZoneId, QuestionId, Count1)
AS
(
 Select 
 tz.ZoneId, 
 qn.QuestionId,
 Count(1) as Count1
 From 
 Customers cust 
 Inner Join Shops td on td.ShopCode = cust.ShopCode
 Inner Join Zones tz on tz.Id = td.SlsZone
 Inner Join Responses res on res.SampleId = cust.SampleId
 Inner Join QstNodes qn on qn.QuestionId = res.QstNodeId
 Where 
 Event >= 201201 And Event <= 201212
 Group BY 
 tz.ZoneId, qn.QuestionId
)
SELECT 
 ZoneId,
 QuestionId
 Count1, 
 (
 SELECT SUM(Count1)
 FROm Cte1 as innercte
 Where innercte.ZoneId = outercte.ZoneId 
 )
 as ZoneTotal
FROM 
 Cte1 outercte

• Gordon Linoff: The answer to your question is rollup, but that depends on the database that you are using.
• saj: @Gordon Linoff. Rollup was the the answer to my question. thanks

Answer 1

Using OVER, you can do it in one pass:

With Cte1 (ZoneId, QuestionId, Count1, sum1, rn)
AS
(
 Select 
 tz.ZoneId, 
 qn.QuestionId 
 , COUNT(1) OVER(PARTITION BY tz.ZoneId, qn.QuestionId) AS "Count1" 
 , COUNT(1) OVER(PARTITION BY tz.ZoneId) AS "Sum1"
 , row_number() OVER(PARTITION BY tz.ZoneId, qn.QuestionId order by ShopCode) AS "rn"
 From 
 Customers cust 
 Inner Join Shops td on td.ShopCode = cust.ShopCode
 Inner Join Zones tz on tz.Id = td.SlsZone
 Inner Join Responses res on res.SampleId = cust.SampleId
 Inner Join QstNodes qn on qn.QuestionId = res.QstNodeId
 Where 
 Event >= 201201 And Event <= 201212
)
SELECT *
FROM 
 Cte1 outercte 
where 
 rn = 1;

• Jamal: Answers should review the original code, not just present a code snippet.
• paparazzo: @Jamal That is not a snippet. That is complete code. Check my SQL related rep on stackoverflow.
• Jamal: What I mean is, answers here shouldn't just contain code.
• paparazzo: @Jamal If that intro is not enough feel free to delete
• Jamal: Good enough for me.

Answer 2

how about using CASE

SELECT tz.ZoneId,
 qn.QuestionId,
 SUM(CASE WHEN Event >= 201201 AND Event <= 201212
 THEN 1 
 ELSE 0
 END) as Count1,
 COUNT(*) AS ZoneTotal
FROM Customers cust
 INNER JOIN Shops td
 ON td.ShopCode = cust.ShopCode
 INNER JOIN Zones tz
 ON tz.Id = td.SlsZone
 INNER JOIN Responses res
 ON res.SampleId = cust.SampleId
 INNER JOIN QstNodes qn
 ON qn.QuestionId = res.QstNodeId
GROUP BY tz.ZoneId, qn.QuestionId

UPDATE 1

The following illustrated on the demo has different records but that thought is the same.

• SQLFiddle Demo Link