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

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;

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