SQL query to obtain totals and subtotals 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 &gt;= 201201 And Event &lt;= 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
how about using CASE

SELECT  tz.ZoneId,
        qn.QuestionId,
        SUM(CASE WHEN Event &gt;= 201201 AND Event &lt;= 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