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