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 not supported for windowing aggregates.
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
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