3. Elements of SQL Statements : SQL Functions : Window Functions : Windowing Aggregate Functions
 
Share this page                  
Windowing Aggregate Functions
Aggregate functions can be used as window functions; that is, you can use the OVER clause with aggregate functions.
Note:  The ORDER BY clause in the window specification is required with frame specifications, but is not supported for windowing aggregates without frame specifications.
Example:
This query computes, for each partition, the aggregate over the rows in that partition. 
SELECT OrderID, ProductID, OrderQty
     ,SUM(OrderQty) OVER(PARTITION BY OrderID) AS "Total"
     ,AVG(OrderQty) OVER(PARTITION BY OrderID) AS "Avg"
     ,COUNT(OrderQty) OVER(PARTITION BY OrderID) AS "Count"
     ,MIN(OrderQty) OVER(PARTITION BY OrderID) AS "Min"
     ,MAX(OrderQty) OVER(PARTITION BY OrderID) AS "Max"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553);
Results:
  OrderID   ProductID   OrderQty   Total   Avg   Count   Min   Max
32548         776          1      26     2      12     1     6
32548         777          3      26     2      12     1     6
32548         778          1      26     2      12     1     6
32548         771          1      26     2      12     1     6
32548         772          1      26     2      12     1     6
32548         773          2      26     2      12     1     6
32548         774          1      26     2      12     1     6
32548         714          3      26     2      12     1     6
32548         716          1      26     2      12     1     6
32548         709          6      26     2      12     1     6
32548         712          2      26     2      12     1     6
32548         711          4      26     2      12     1     6
32553         772          1      14     1       8     1     4
32553         775          4      14     1       8     1     4
32553         714          1      14     1       8     1     4
32553         716          1      14     1       8     1     4
32553         777          2      14     1       8     1     4
32553         771          3      14     1       8     1     4
32553         773          1      14     1       8     1     4
32553         778          1      14     1       8     1     4
The multiple coding of the window specification in this query could be avoided as follows:
SELECT OrderID, ProductID, OrderQty
     ,SUM(OrderQty) OVER win1 AS "Total"
     ,AVG(OrderQty) OVER win1 AS "Avg"
     ,COUNT(OrderQty) OVER win1 AS "Count"
     ,MIN(OrderQty) OVER win1 AS "Min"
     ,MAX(OrderQty) OVER win1 AS "Max"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553)
WINDOW win1 AS (PARTITION BY OrderID);
When the OVER clause is used without a window specification the function results are computed across all rows in the table. The following example returns the row count of the table for each row:
CREATE TABLE foobar(x INTEGER NOT NULL);
INSERT INTO foobar VALUES (1), (2), (2), (3), (3), (3);
SELECT x
    ,ROW_NUMBER() OVER(ORDER BY x) AS row_nbr
    ,COUNT(*) OVER() AS row_cnt
FROM foobar
 
x           row_nbr     row_cnt
-------------------------------
      1           1           6
      2           2           6
      2           3           6
      3           4           6
      3           5           6
      3           6           6
Example:
This query computes, for each partition, the sum over the rows in that partition up to the current row as ordered by the ProductID column. 
SELECT OrderID, ProductID, OrderQty,
SUM(OrderQty) OVER(PARTITION BY OrderID ORDER BY ProductID ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS "Running Total"
FROM SalesOrderDetail
WHERE OrderID IN(32548,32553);
Results:
OrderID    ProductID     OrderQty         Running Total
-------------------------------------------------------
  32548          709            6                     6
  32548          711            4                    10
  32548          712            2                    12
  32548          714            3                    15
  32548          716            1                    16
  32548          771            1                    17
  32548          772            1                    18
  32548          773            2                    20
  32548          774            1                    21
  32548          776            1                    22
  32548          777            3                    25
  32548          778            1                    26
  32553          714            1                     1
  32553          716            1                     2
  32553          771            3                     5
  32553          772            1                     6
  32553          773            1                     7
  32553          775            4                    11
  32553          777            2                    13
  32553          778            1                    14