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
-
- 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.
-
- SELECT *, MAX(SALES_AMOUNT) OVER() [MAXLSALES] FROM DBO.SALES
We can also specify the partition clause in the over function.
-
- 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:
- UNBOUNDED PRECEDING OR FOLLOWING: from the beginning or ending of the rows-based partition by clause.
- CURRENT ROW: the current row.
- 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