Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Jan 24 '13 at 15:45

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

1  
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
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

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.