Example: Multiple aggregate functions in a query

This example calculates aggregate values against different windows in a query.

SELECT prod_id, month_num, sales, AVG(sales) OVER 
  (WS1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
  CAvg, SUM(sales) OVER(WS1 ROWS BETWEEN UNBOUNDED
  PRECEDING AND CURRENT ROW) AS CSum 
FROM sale WHERE rep_id = 1 WINDOW WS1 AS (PARTITION BY
  prod_id 
ORDER BY month_num) 
ORDER BY prod_id, month_num;

The following are the results from the above query:

prod_id  month_num    sales     CAvg     CSum
-------  ---------    -----     ----     ----
10               1      100   110.00      100
10               2      120   106.66      220
10               3      100   116.66      320
10               4      130   116.66      450
10               5      120   120.00      570
10               6      110   115.00      680
20               1       20    25.00       20
20               2       30    25.00       50
20               3       25    28.33       75
20               4       30    28.66      105
20               5       31    27.00      136
20               6       20    25.50      156
30               1       10    10.50       10
30               2       11    11.00       21
30               3       12     8.00       33
30               4        1     6.50       34