Return to Revisions

Using over

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;