Windows Aggregate Functions in SQL Server

In this article you will learn about Windows Aggregate function 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

  1. CREATE TABLE DBO.SALES (  
  2. PROD_ID INT, SALES_YEAR INT, SALES_AMOUNT INT  
  3. INSERT INTO DBO.SALES(  
  4. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  5. )  
  6. VALUES  
  7. (1, 2009, 10000) INSERT INTO DBO.SALES(  
  8. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  9. )  
  10. VALUES  
  11. (1, 2010, 9000) INSERT INTO DBO.SALES(  
  12. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  13. )  
  14. VALUES  
  15. (1, 2011, 8000) INSERT INTO DBO.SALES(  
  16. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  17. )  
  18. VALUES  
  19. (1, 2012, 7000) INSERT INTO DBO.SALES(  
  20. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  21. )  
  22. VALUES  
  23. (1, 2013, 14000) INSERT INTO DBO.SALES(  
  24. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  25. )  
  26. VALUES  
  27. (1, 2014, 18000) INSERT INTO DBO.SALES(  
  28. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  29. )  
  30. VALUES  
  31. (1, 2015, 15000) INSERT INTO DBO.SALES(  
  32. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  33. )  
  34. VALUES  
  35. (2, 2013, 12000) INSERT INTO DBO.SALES(  
  36. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  37. )  
  38. VALUES  
  39. (2, 2014, 8000) INSERT INTO DBO.SALES(  
  40. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  41. )  
  42. VALUES  
  43. (2, 2015, 16000) INSERT INTO DBO.SALES(  
  44. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  45. )  
  46. VALUES  
  47. (3, 2012, 7000) INSERT INTO DBO.SALES(  
  48. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  49. )  
  50. VALUES  
  51. (3, 2013, 8000) INSERT INTO DBO.SALES(  
  52. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  53. )  
  54. VALUES  
  55. (3, 2014, 9700) INSERT INTO DBO.SALES(  
  56. PROD_ID, SALES_YEAR, SALES_AMOUNT  
  57. )  
  58. VALUES  
  59. (3, 2015, 12500)  
  60. SELECT  
  61. *  
  62. FROM  
  63. DBO.SALES  
table
  1. --SUM OF SALES TOTAL TO ALL ROWS USING WINDOW AGGREGATE FUNCTION  
  2. SELECT *, FORMAT(SUM(SALES_AMOUNT) OVER(),'C') [SUM_TOTALSALES] FROM DBO.SALES  
Or:
  1. 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.

some of total sales
  1. --MAX OF SALES TOTAL FROM ROWS TO ALL ROWS USING WINDOW AGGREGATE FUNCTION  
  2. SELECT *, MAX(SALES_AMOUNT) OVER() [MAXLSALES] FROM DBO.SALES  
max sale amount

We can also specify the partition clause in the over function.
  1. --SUM OF SALES TOTAL TO ALL ROWS USING WINDOW AGGREGATE FUNCTION WITH PARTITION BY  
  2. SELECT  
  3. *,  
  4. SUM(SALES_AMOUNT) OVER(PARTITION BY PROD_ID) [SUM_TOTALSALES_WITHPARTITIONBY]  
  5. FROM  
  6. DBO.SALES  
run
  1. SELECT  
  2. PROD_ID,  
  3. SALES_YEAR,  
  4. FORMAT(SALES_AMOUNT, 'C'AS SALES_AMOUNT,  
  5. FORMAT(  
  6. SUM(SALES_AMOUNT) OVER(PARTITION BY PROD_ID),  
  7. 'C'  
  8. ) [SUM_PROD_TOTALSALES],  
  9. FORMAT(  
  10. CAST(SALES_AMOUNT AS FLOAT)/ SUM(  
  11. CAST(SALES_AMOUNT AS FLOAT)  
  12. ) OVER(PARTITION BY PROD_ID),  
  13. 'P'  
  14. AS [PERCENT_PROD]  
  15. FROM  
  16. DBO.SALES  
output
  1. SELECT  
  2. PROD_ID,  
  3. SUM(SALES_AMOUNT) AS [PROD_TOTAL_SALES],  
  4. SUM(  
  5. SUM(SALES_AMOUNT)  
  6. ) OVER(  
  7. ORDER BY  
  8. SUM(SALES_AMOUNT)  
  9. AS [ALL_GROUP_RUNNING_TOTAL]  
  10. FROM  
  11. DBO.SALES  
  12. GROUP BY  
  13. PROD_ID  
aggregate functions

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.

PRECEEDING OR FOLLOWING

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.

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

The following shows the Sum of previous running totals with a partition by clause:
  1. SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT  
  2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES  
Or:
  1. SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT  
  2. 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.

Previous running totals

The following is a sample of Sum of the next running totals with a partition by clause:
  1. SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT  
  2. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES  
Sum of Next running totals

The following is a sample of Sum of the Previous 2 rows totals and the current row:
  1. SELECT *, SUM(SALES_AMOUNT) OVER( PARTITION BY PROD_ID ORDER BY SALES_AMOUNT  
  2. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) [SUM_TOTALSALES_WITHPARTITIONBY] FROM DBO.SALES  
Sum of Previous 2 rows