Microsoft® SQL Server® 2012 Bible

267

Chapter 10: Aggregating, Windowing, and Ranking Data

10

soh.TotalDue,

SUM(soh.TotalDue) OVER

(ORDER BY sp.FirstName ROWS UNBOUNDED PRECEDING)

CumulativeTotal

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.vSalesPerson sp

ON soh.SalesPersonID = sp.BusinessEntityID

WHERE

soh.SalesPersonID IN (274) AND

soh.OrderDate BETWEEN '1/1/2005' AND '12/31/2005'

Results:

FirstName LastName OrderYear TotalDue CumulativeTotal

---------- -------- ----------- ----------- ---------------

Stephen Jiang 2005 23130.2957 23130.2957

Stephen Jiang 2005 2297.0332 25427.3289

Stephen Jiang 2005 4723.1073 30150.4362

Stephen Jiang 2005 2417.4793 32567.9155

In the preceding query the UNBOUND PRECEDING argument tells the query engine to

continually aggregate the Total Due column until the end of the set. The same results are

returned if the query is slightly changed to the following:

USE AdventureWorks

GO

SELECT

sp.FirstName,

sp.LastName,

Year(soh.OrderDate) OrderYear,

soh.TotalDue,

SUM(soh.TotalDue) OVER

(ORDER BY sp.FirstName ROWS BETWEEN CURRENT ROW AND UNBOUNDED

FOLLOWING) CumulativeTotal

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.vSalesPerson sp

ON soh.SalesPersonID = sp.BusinessEntityID

WHERE

soh.SalesPersonID IN (274) AND

soh.OrderDate BETWEEN '1/1/2005' AND '12/31/2005'

The preceding example illustrates the use of ROWS and BETWEEN together to physically limit

the results of the cumulative total by a range that starts at the current row and ends at the

end of the set. The end of the set is specifi ed by the UNBOUNDED FOLLOWING argument.

Previous and Current Row

The true value in the new OVER() arguments is their capability to access previous and next

rows. The following query introduces an additional modifi cation to the PRECEDING argu-

ment that was defi ned earlier:

c10.indd 267c10.indd 267 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM

http://www.it-ebooks.info