Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – OVER ( … ROWS / RANGE … )

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

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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
Categories: SQLServer, SQLServer 2012
  1. August 24, 2012 at 3:19 pm

    How about this query?

    select D1.*,(select sum(amount) from SalesData C1 where C1.Month<=D1.Month ) total
    from SalesData D1
    order by month

  2. Rafeea
    October 29, 2012 at 12:36 pm

    hi, how would you adapt this for sql 2008? the ‘rows 1 preceding’ does not work in sql 2008….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: