2
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ The answer to your question is rollup, but that depends on the database that you are using. \$\endgroup\$ – Gordon Linoff Jan 24 '13 at 15:31
  • \$\begingroup\$ @Gordon Linoff. Rollup was the the answer to my question. thanks \$\endgroup\$ – saj Jan 24 '13 at 16:48
1
\$\begingroup\$

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;
\$\endgroup\$
  • \$\begingroup\$ Answers should review the original code, not just present a code snippet. \$\endgroup\$ – Jamal Aug 14 '16 at 6:38
  • \$\begingroup\$ @Jamal That is not a snippet. That is complete code. Check my SQL related rep on stackoverflow. \$\endgroup\$ – paparazzo Aug 14 '16 at 6:46
  • \$\begingroup\$ What I mean is, answers here shouldn't just contain code. \$\endgroup\$ – Jamal Aug 14 '16 at 6:48
  • \$\begingroup\$ @Jamal If that intro is not enough feel free to delete \$\endgroup\$ – paparazzo Aug 14 '16 at 7:10
  • \$\begingroup\$ Good enough for me. \$\endgroup\$ – Jamal Aug 14 '16 at 7:12
3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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