Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
        Count(1) as Count1
        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
        Event >= 201201 And Event <= 201212
    Group BY 
        tz.ZoneId, qn.QuestionId
        SELECT SUM(Count1)
        FROm Cte1 as innercte
        Where innercte.ZoneId = outercte.ZoneId 
    as ZoneTotal
    Cte1 outercte
share|improve this question

migrated from Jan 24 '13 at 15:45

This question came from our site for professional and enthusiast programmers.

The answer to your question is rollup, but that depends on the database that you are using. – Gordon Linoff Jan 24 '13 at 15:31
@Gordon Linoff. Rollup was the the answer to my question. thanks – saj Jan 24 '13 at 16:48

how about using CASE

SELECT  tz.ZoneId,
        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


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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.