Windows Aggregate Functions in SQL Server

The Windows aggregate functions are normal aggregate functions, the normal aggregate functions have some limitations.

In the normal aggregate functions we need to use a Group by clause or hide some of the columns.
The normal aggregate functions like Sum, Max, Min, Avg and Count can return the grouped data, but in the Windows aggregate functions they can return the row by row aggregated data.

Demo

CREATE TABLE DBO.SALES (
PROD_ID INT, SALES_YEAR INT, SALES_AMOUNT INT
) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2009, 10000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2010, 9000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2011, 8000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2012, 7000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2013, 14000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2014, 18000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(1, 2015, 15000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(2, 2013, 12000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(2, 2014, 8000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(2, 2015, 16000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(3, 2012, 7000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(3, 2013, 8000) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(3, 2014, 9700) INSERT INTO DBO.SALES(
PROD_ID, SALES_YEAR, SALES_AMOUNT
)
VALUES
(3, 2015, 12500)
SELECT
*
FROM
DBO.SALES

--SUM OF SALES TOTAL TO ALL ROWS USING WINDOW AGGREGATE FUNCTION
SELECT *, FORMAT(SUM(SALES_AMOUNT) OVER(),'C') [SUM_TOTALSALES] FROM DBO.SALES

Or:

SELECT *, (SELECT FORMAT(SUM(SALES_AMOUNT) ,'C') FROM DBO.SALES) AS [SUM_TOTALSALES] FROM DBO.SALES

Without using Windows aggregate functions by using a sub query we can get the same result, compared to the Windows aggregate functions that provide better performance.

--MAX OF SALES TOTAL FROM ROWS TO ALL ROWS USING WINDOW AGGREGATE FUNCTION
SELECT *, MAX(SALES_AMOUNT) OVER() [MAXLSALES] FROM DBO.SALES

We can also specify the partition clause in the over function.

--SUM OF SALES TOTAL TO ALL ROWS USING WINDOW AGGREGATE FUNCTION WITH PARTITION BY
SELECT
*,
SUM(SALES_AMOUNT) OVER(PARTITION BY PROD_ID) [SUM_TOTALSALES_WITHPARTITIONBY]
FROM
DBO.SALES

SELECT
PROD_ID,
SALES_YEAR,
FORMAT(SALES_AMOUNT, 'C') AS SALES_AMOUNT,
FORMAT(
SUM(SALES_AMOUNT) OVER(PARTITION BY PROD_ID),
'C'
) [SUM_PROD_TOTALSALES],
FORMAT(
CAST(SALES_AMOUNT AS FLOAT)/ SUM(
CAST(SALES_AMOUNT AS FLOAT)
) OVER(PARTITION BY PROD_ID),
'P'
) AS [PERCENT_PROD]
FROM
DBO.SALES

SELECT
PROD_ID,
SUM(SALES_AMOUNT) AS [PROD_TOTAL_SALES],
SUM(
SUM(SALES_AMOUNT)
) OVER(
ORDER BY
SUM(SALES_AMOUNT)
) AS [ALL_GROUP_RUNNING_TOTAL]
FROM
DBO.SALES
GROUP BY
PROD_ID

Window aggregate functions can support Window frame clause.

Window frame clause can be:

 

1. UNBOUNDED PRECEDING OR FOLLOWING: from the beginning or ending of the rows-based partition by clause.
   
   
2. CURRENT ROW: the current row.
   
   
3. N ROWS PRECEEDING OR FOLLOWING: N rows before or after.

When we define a Windows aggregate function if we provide an order by clause without a frame clause then by default SQL Server provides RANGE BETWEEN UNBOUNDED PRECEEDING AND CUURENT ROW.

SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES

The following shows the Sum of previous running totals with a partition by clause:

SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES

Or:

SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT
ROWS UNBOUNDED PRECEDING ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES

In the preceding two queries ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW OR ROWS UNBOUNDED PRECEDING has the same meaning.



The following is a sample of Sum of the next running totals with a partition by clause:

SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES

The following is a sample of Sum of the Previous 2 rows totals and the current row:

SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES