In-depth discussion of SQL Server 2005 window functions

Windows functions in SQL Server 2005 (window function) are not related to Microsoft Windows; Instead, they create data windows. Window functions help you quickly look at different levels of aggregation, which make it easy to recover cumulative totals, move averages, and perform other calculations.

A SQL Server window is a partition of the rows that are applied to a function. You use over (...) clause to specify a window, you can apply this clause to any one of the aggregate functions. Generally, you divide the data into groups, but the arguments for over () are optional.

USE AdventureWorks2;
GO
SELECT SalesOrderID, ProductID,
OrderQty AS 'Item Qty',SUM(OrderQty)
OVER(PARTITION BY SalesOrderID) AS 'Total Qty',
AVG(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Average Qty',
COUNT(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Total Count',
MIN(OrderQty) OVER(PARTITION BY SalesOrderID)
AS 'Minimum Count',
MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS
'Maximum Count',LineTotal,
AVG(LineTotal) OVER(PARTITION BY SalesOrderID) AS
'Average Amount',
SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS
'Total Amount',(LineTotal/SUM(LineTotal)
OVER(PARTITION BY SalesOrderID)) * 100 AS
'Percent'FROM Sales.SalesOrderDetail WHERE
SalesOrderID = 43664ORDER BY ProductID
GO

List A

List B

List A shows how to collect several aggregations at a time, or even other calculations based on aggregations. This allows you to get the result set in List B. I limit the result set to a single OrderID, then the query will produce only one window. You can modify clauses in a variety of ways, such as using an in () clause to list a specific set of orders, or using a between clause to derive a statistical value in a sequential range. Finally, each OrderID you specify gets a window.

The window function is very powerful and easy to build. You can use this technique to immediately search for a large number of statistics. The example in this tutorial shows that you can search for any collection of statistics in a single query.