Sql query to obtain totals and subtotals

Question

I'm interested in knowing if there's a better/cleaner/more efficient way, to obtain totals and subtotals within a query, than my solution below.

The query works fine but I'm just intrigued to know if there is 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

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