Aggregate Window Functions

You already know aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX(). When you add an OVER() clause to these functions, they become window functions -- they compute the aggregate while preserving every individual row in your result set.

Basic Aggregate Windows

SUM() OVER()

Without ORDER BY in the OVER() clause, the aggregate is computed across the entire partition:

-- Each row shows its amount alongside the total of ALL sales
SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER () AS total_sales
FROM sales
ORDER BY sale_date;

Every row gets the same total_sales value because the window is the entire table.

PARTITION BY with Aggregates

Add PARTITION BY to compute the aggregate within groups:

-- Each row shows its amount alongside its region's total
SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  COUNT(*) OVER (PARTITION BY region) AS region_count,
  AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales
ORDER BY region, sale_date;

This is incredibly useful because you can see individual row data alongside group-level summaries without needing a self-join or subquery.

Percentage of Total

A common pattern is calculating what percentage each row contributes:

SELECT
  region,
  amount,
  SUM(amount) OVER () AS grand_total,
  ROUND(amount * 100.0 / SUM(amount) OVER (), 2) AS pct_of_total
FROM sales
ORDER BY amount DESC;

Or the percentage within a group:

SELECT
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY region), 2) AS pct_of_region
FROM sales
ORDER BY region, amount DESC;

Running Totals

When you add ORDER BY inside OVER(), aggregate functions become cumulative. This is one of the most powerful features of window functions.

-- Running total of sales by date
SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Each row's running_total is the sum of all amounts from the first row up through the current row.

Running Totals per Group

Combine PARTITION BY and ORDER BY for running totals within groups:

-- Running total per region
SELECT
  region,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS region_running_total
FROM sales
ORDER BY region, sale_date;

The running total resets at the start of each region.

Running Count and Running Average

The same cumulative behavior applies to other aggregates:

SELECT
  sale_date,
  amount,
  COUNT(*) OVER (ORDER BY sale_date) AS running_count,
  AVG(amount) OVER (ORDER BY sale_date) AS running_avg,
  MIN(amount) OVER (ORDER BY sale_date) AS running_min,
  MAX(amount) OVER (ORDER BY sale_date) AS running_max
FROM sales
ORDER BY sale_date;

Window Frame Specification

The frame clause gives you precise control over which rows the window function considers. The full syntax is:

function() OVER (
  [PARTITION BY ...]
  ORDER BY ...
  frame_type BETWEEN frame_start AND frame_end
)

Frame Types

• ROWS -- counts individual rows
• RANGE -- groups rows with the same ORDER BY value (the default when ORDER BY is specified)
• GROUPS -- counts peer groups

Frame Boundaries

Moving Average (Sliding Window)

A 3-row moving average smooths out fluctuations:

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM sales
ORDER BY sale_date;

This computes the average of the current row and the two rows before it.

Fixed Look-Back and Look-Ahead

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS surrounding_sum
FROM sales
ORDER BY sale_date;

This sums the previous row, current row, and next row for each position.

Full Range Windows

Sometimes you want to compute from the start up to the current row, or from the current row to the end:

SELECT
  sale_date,
  amount,
  -- Sum from start to current row (this is the default with ORDER BY)
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_sum,
  -- Sum from current row to end
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS reverse_cumulative
FROM sales
ORDER BY sale_date;

The Default Frame

Understanding the default frame is important to avoid surprises:

• With ORDER BY: The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (cumulative)
• Without ORDER BY: The default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (entire partition)

This is why SUM() OVER (ORDER BY sale_date) gives a running total, while SUM() OVER () gives the grand total.

Named Windows

If you use the same window specification multiple times, you can define it once with WINDOW:

SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER w AS running_total,
  AVG(amount) OVER w AS running_avg,
  COUNT(*) OVER w AS running_count
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date)
ORDER BY region, sale_date;

This keeps your query clean and ensures consistency.

Try It Yourself

1. Calculate the running total of sales amounts ordered by sale_date. What is the total after all 50 rows?

2. Compute a 5-row moving average of amounts. How does it compare to the overall average?

3. For each sale, show the amount and what percentage it represents of its region's total sales.

4. Use PARTITION BY region with ORDER BY sale_date to get running totals within each region. Which region reaches $1000 fastest?