One of the features of SQL Server 2005 that does not get a lot of press is the new window-based calculation clause called OVER. You can now generate different aggregates with different grouping columns in the same query, without having to use subqueries. This is especially useful when you need to include aggregated values along side detail data in the same result set or compute percentages based on different groupings.
The OVER clause is mostly used when performing the new RANK, DENSE_RANK, ROW_NUMBER and NTILE functions. But it can be used with standard scalar aggregates like SUM, AVG, etc..
The following is an example of using the OVER clause with the PARTITION BY to generate a couple of different aggregates each with a different grouping (partitioning). If you have the Adventureworks database with SQL Server 2005 you can run these sample queries.
SELECT [group] CountryGroup
, name Territory
, SUM(salesytd) OVER (PARTITION BY [group], [name]) AS TerritoryTotal
, SUM(salesytd) OVER (PARTITION BY [group]) AS GroupTotal
, (SUM(salesytd) OVER (PARTITION BY [group], [name]) /
SUM(salesytd) OVER (PARTITION BY [group])) * 100.0 AS PercentofGroup
FROM sales.salesTerritory
ORDER BY [group]
The following is an equivalent query using derived table subqueries:
SELECT g.[group] CountryGroup
, t.name Territory
, TerritoryTotal
, GroupTotal
, TerritoryTotal / GroupTotal * 100.0 PercentofGroup
FROM (SELECT [group]
, SUM(salesytd) GroupTotal
FROM sales.salesterritory
GROUP BY [group]) g
INNER JOIN
(SELECT [group]
, name
, SUM(salesytd) TerritoryTotal
FROM sales.salesterritory
GROUP BY [group], name) t
ON g.[group] = t.[group]
ORDER BY [group], name
