In SQL Server 2012, one of the additions to OVER clause is, ROWS Clause.
In general, when we apply aggregate functions in over clause, it will apply to all rows in that group/partition.
Suppose, if we wants to restrict the number of rows, it involves some complexity. Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.
Now in SQL Server 2012, Over Clause is enhanced with ROWS Clause, which allows specifying number of rows to consider while applying aggregates
DECLARE @Companies_Revenue TABLE(CompanyName varchar(10),[Year] int,Amount int)
SELECT COMPANYNAME,
YEAR,
AMOUNT,
SUM(AMOUNT) OVER (PARTITION BY CompanyName ORDER BY YEAR ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) [NEXT 3 YEARS REVNUE],
SUM(AMOUNT) OVER(PARTITION BY CompanyName ORDER BY YEAR ROWS UNBOUNDED PRECEDING)
[RANGE CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER(PARTITION BY CompanyNameORDER BY YEAR ROWS UNBOUNDED PRECEDING) [CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER (PARTITION BY CompanyNameORDER BY YEARROWS BETWEEN 1 PRECEDING AND CURRENT ROW)[Last 3 Years Revenue]
FROM @COMPANIES_REVENUEINSERT INTO @Companies_Revenue (CompanyName,[Year],[Amount])VALUES ('ABC',2000,100000),('ABC',2001,200000),('ABC',2002,35000),('DEF',2000,50000),('DEF',2001,75000),('DEF',2002,35000)
In general, when we apply aggregate functions in over clause, it will apply to all rows in that group/partition.
Suppose, if we wants to restrict the number of rows, it involves some complexity. Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.
Now in SQL Server 2012, Over Clause is enhanced with ROWS Clause, which allows specifying number of rows to consider while applying aggregates
DECLARE @Companies_Revenue TABLE(CompanyName varchar(10),[Year] int,Amount int)
SELECT COMPANYNAME,
YEAR,
AMOUNT,
SUM(AMOUNT) OVER (PARTITION BY CompanyName ORDER BY YEAR ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) [NEXT 3 YEARS REVNUE],
SUM(AMOUNT) OVER(PARTITION BY CompanyName ORDER BY YEAR ROWS UNBOUNDED PRECEDING)
[RANGE CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER(PARTITION BY CompanyNameORDER BY YEAR ROWS UNBOUNDED PRECEDING) [CUMMILATEIVE AMOUNT],
SUM(AMOUNT) OVER (PARTITION BY CompanyNameORDER BY YEARROWS BETWEEN 1 PRECEDING AND CURRENT ROW)[Last 3 Years Revenue]
FROM @COMPANIES_REVENUEINSERT INTO @Companies_Revenue (CompanyName,[Year],[Amount])VALUES ('ABC',2000,100000),('ABC',2001,200000),('ABC',2002,35000),('DEF',2000,50000),('DEF',2001,75000),('DEF',2002,35000)
