Monday 21 July 2008

OVER Clause in SQL 2005

Converting some reports to SQL2005, I initially forgot the OVER clause can be used aith aggregate functions (had previously only used with RANK).
Saves using multiple joins and GROUP BY record sets >

SELECT   [Name] StateName
  ,[City]
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) AS CityTotal
  ,COUNT([Name]) OVER (PARTITION BY [Name]) AS StateTotal
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) / CONVERT(FLOAT,COUNT([Name]) OVER (PARTITION BY [Name])) * 100.0 AS PercentOfState
FROM Person.Address a
INNER JOIN Person.StateProvince s
 ON a.StateProvinceID = s.StateProvinceID

No comments: