Window Aggregates

Standard aggregate functions (SUM, AVG, COUNT) can also be used as window functions. This allows you to calculate cumulative statistics (like a running total) or relative metrics (like % of total) without collapsing your data.

Definition: The Window Aggregate

When you add an OVER() clause to an aggregate function, it changes from a “collapsing” operation to a “windowing” operation.

Context: Running Totals

A Running Total is a cumulative sum of a column where each row shows the sum of all preceding values plus itself. To create a running total, you must include an ORDER BY inside the OVER() clause.

Running Total Logic

SUM(Amount) OVER(ORDER BY Date)

Detailed Explanation: Syntax and Comparison

1. Cumulative Sum (Running Total)

Requires ORDER BY. SUM(Sales) OVER(ORDER BY Month)

2. Grand Total (On every row)

Requires an empty OVER(). SUM(Sales) OVER()

3. Sub-Total (By category)

Requires PARTITION BY. SUM(Sales) OVER(PARTITION BY Region)

Example: Daily Revenue Analysis

We have a DailyRevenue table:

Date	Revenue
2023-01-01	100
2023-01-02	150
2023-01-03	200

Task: Show daily revenue, the running total, and what % of the company’s total revenue that day represents.

Mixed Windowing

SELECT
  Date,
  Revenue,
  SUM(Revenue) OVER(ORDER BY Date) AS RunningTotal,
  SUM(Revenue) OVER() AS GrandTotal,
  (Revenue * 100.0) / SUM(Revenue) OVER() AS PctOfTotal
FROM DailyRevenue;

Output

Date	Revenue	RunningTotal	GrandTotal	PctOfTotal
2023-01-01	100	100	450	22.2%
2023-01-02	150	250	450	33.3%
2023-01-03	200	450	450	44.4%

Under the Hood: The Default Frame

Why does ORDER BY turn a sum into a running total?

When you include an ORDER BY in a window aggregate, the database applies a default Window Frame. It tells the function to only look at: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means: “Start from the very first row of the partition and stop at the row I am currently processing.” Without the ORDER BY, the default frame is the entire partition.

Performance Tip

If you need to calculate both a running total and a grand total, use two separate window functions as shown in the example. The database engine is smart enough to share the same table scan for both, making it highly efficient.

Caution

Be careful with duplicate values in your ORDER BY. If two rows have the same date, some databases will add them both to the running total at the same time (causing a jump), while others might process them sequentially. For consistent running totals, always include a unique column (like ID) in your window’s ORDER BY.