with scores as (select month, location, score6=case when score < 7 then 1 else 0 end, score79=case when score > 6 and score < 9 then 1 else 0 end, score9=case when score > 9 then 1 else 0 end from table)
select month, location, score6=sum(score6) over (partition by month,location),score79=sum(score79) over (partition by month,location),score9=sum(score9) over (partition by month,location)
from scores

With Scores AS
	 (SELECT ShortName, MONTH(CollectionDate) M, Year(CollectionDate) Y,
	         Pro = CASE WHEN RecommendNPS > 8
			            THEN 1
						ELSE 0
					END,
			 Neu = CASE WHEN RecommendNPS Between 7 and 8
			            THEN 1
						ELSE 0
					END,
			 Det = CASE WHEN RecommendNPS < 7
			            THEN 1
						ELSE 0
					END
	   FROM SatSurvey
	  WHERE CollectionDate between '4/1/2017' and '4/30/2018')
SELECT ShortName, M, Y,
	   Sum(Pro) Promoter, Sum(Neu) Neutral, SUM(Det) Detractor