Using [over][1] 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; [1]: https://msdn.microsoft.com/en-us/library/ms189461.aspx