Archive

Posts Tagged ‘running total’

SQL Server – "Denali" – OVER ( … ROWS / RANGE … )

17.08.2011 2 comments

ROWS/RANGE allows you to change the frame size within a partition. Valid arguments for ROWS are:

 

UNBOUNDED PRECEDING Starts the window at first row of the partition
UNBOUNDED FOLLOWING Ends the window at last row of the partition
CURRENT ROW Starts or Ends the window at current row
N PRECEDING Starts the window N rows before current row
N FOLLOWING Ends the window N rows after current row

 

RANGE is only supported with UNBOUNDED and CURRENT ROW frame delimiters, It cannot be used with N PRECEDING or N FOLLOWING.

 

ROWS UNBOUNDED PRECEDING:

A simple example to demonstrate use of ROWS is generating cumulative total. Consider following data:

Year        Month       Amount

2011        1           1000.00

2011        2           2000.00

2011        3           3000.00

2011        4           4000.00

2011        5           5000.00

2011        6           6000.00

2011        7           7000.00

To generate a cumulative total we need sum of all rows till current row. This can be done by using UNBOUNDED PRECEDING:

SELECT [Year], [Month],

       SUM([Amount]) OVER (ORDER BY [Year], [Month] ROWS UNBOUNDED PRECEDING)

       AS [Amount]

FROM   dbo.SalesData

GROUP BY [Year], [Month], [Amount]

When no starting or ending position is defined, it defaults to CURRENT ROW. I.e. for Month = 3, Amount is generated as sum of Amount of Month = 1 + Amount of Month = 2 + Amount of Month = 3

Result Set:

Year        Month       Amount

2011        1          1000.00

2011        2          3000.00

2011        3          6000.00

2011        4          10000.00

2011        5           15000.00

2011        6           21000.00

2011        7           28000.00

 

ROWS BETWEEN N PRECEDING AND CURRENT ROW:

By using N PRECEDING you can specify to start frame N rows before current row. For example, to generate Amount by current month + previous month:

SELECT [Year], [Month],

       SUM([Amount]) OVER (ORDER BY [Year], [Month] ROWS 1 PRECEDING)

       –End default to CURRENT ROW

       –Or you can also write

       –SUM([Amount])

       –OVER (ORDER BY [Year], [Month] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

       –To make is more readable

       AS [Amount]

FROM   dbo.SalesData

GROUP BY [Year], [Month], [Amount]

Result set:

Year        Month       Amount

2011        1          1000.00

2011        2           3000.00

2011        3           5000.00

2011        4           7000.00

2011        5           9000.00

2011        6          11000.00

2011        7           13000.00

 

More combinations to try:

CREATE TABLE dbo.SalesData

(     

       [Year]        INT,

       [Month]       INT,

       [Amount]      MONEY

)

 

INSERT INTO dbo.SalesData VALUES

(2011, 1, 1000.00), (2011, 2, 2000.00), (2011, 3, 3000.00),

(2011, 4, 4000.00), (2011, 5, 5000.00), (2011, 6, 6000.00),

(2011, 7, 7000.00)

SELECT [Year], [Month], [Amount],

       –SUM([Amount]) OVER (ORDER BY [Year], [Month] ROWS 1 PRECEDING)

       –SUM([Amount]) OVER (ORDER BY [Year], [Month] ROWS 1 FOLLOWING)

       –SUM([Amount]) OVER (ORDER BY [Year], [Month]

            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

       –SUM([Amount]) OVER (ORDER BY [Year], [Month]

            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

       –SUM([Amount]) OVER (ORDER BY [Year], [Month] RANGE UNBOUNDED PRECEDING)

              AS [SUM]

FROM   dbo.SalesData

GROUP BY [Year], [Month], [Amount]

Hope This Helps!

Vishal