WITH
ctePreAgg AS
(
 SELECT LocationName = loc.Name
      , ContractName = svcc.Name
      , ServiceCount = COUNT(svc.Service)
   FROM dbo.Services         svc
   JOIN dbo.Location         loc  ON loc.ID_Location          = svc.ID_Location
   JOIN dbo.Service_Type     svct ON svct.ID_Service_Type     = svc.ID_Service_type
   JOIN dbo.Service_Contract svcc ON svcc.ID_Service_Contract = svct.ID_Service_Contract
  GROUP BY loc.Name, svcc.Name
)
 SELECT Location
      , FastSpeed   = SUM(CASE WHEN ContractName = 'FastSpeed'   THEN ServiceCount ELSE 0 END)
      , FasterSpeed = SUM(CASE WHEN ContractName = 'FasterSpeed' THEN ServiceCount ELSE 0 END)
      , SlowSpeed   = SUM(CASE WHEN ContractName = 'SlowSpeed'   THEN ServiceCount ELSE 0 END)
      , Total       = SUM(ServiceCount)
   FROM ctePreAgg
  ORDER BY Location
;

quote:

---

Originally posted by Jeff Moden

A good ol' fashioned pre-aggregated CROSS TAB is usually faster than a PIVOT. Like this...

WITH
ctePreAgg AS
(
 SELECT LocationName = loc.Name
      , ContractName = svcc.Name
      , ServiceCount = COUNT(svc.Service)
   FROM dbo.Services         svc
   JOIN dbo.Location         loc  ON loc.ID_Location          = svc.ID_Location
   JOIN dbo.Service_Type     svct ON svct.ID_Service_Type     = svc.ID_Service_type
   JOIN dbo.Service_Contract svcc ON svcc.ID_Service_Contract = svct.ID_Service_Contract
  GROUP BY loc.Name, svcc.Name
)
 SELECT Location
      , FastSpeed   = SUM(CASE WHEN ContractName = 'FastSpeed'   THEN ServiceCount ELSE 0 END)
      , FasterSpeed = SUM(CASE WHEN ContractName = 'FasterSpeed' THEN ServiceCount ELSE 0 END)
      , SlowSpeed   = SUM(CASE WHEN ContractName = 'SlowSpeed'   THEN ServiceCount ELSE 0 END)
      , Total       = SUM(ServiceCount)
   FROM ctePreAgg
  GROUP BY Location
  ORDER BY Location
;

Of course, unsubstantiated claims of performance are just begging for a fight so here's a link that demonstrates it.
http://www.sqlservercentral.com/articles/T-SQL/63681/


--Jeff Moden

---

RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

---