Window Functions Aggregates OVER · PARTITION BY · frame

Aggregate Window Functions

The five aggregates — SUM, AVG, COUNT, MAX, MIN — all double as window functions. Add OVER() and they keep your rows while still calculating per-partition or running totals. Plus the frame clause cheat sheet and the DISTINCT trap.

Aggregates as window functions

The five everyday aggregates — SUM, AVG, COUNT, MIN, MAX — all double as window functions. Add an OVER() clause and they stop collapsing rows; instead each row keeps its detail and picks up the aggregate calculated over its window.

That's the whole trick. Same function name, same behaviour, just a different return shape: one value per row instead of one value per group.

Syntax

{SUM | AVG | COUNT | MIN | MAX}(expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

Part	Effect
OVER ()	Apply the aggregate to the entire result set.
PARTITION BY	Restart the aggregate in each partition.
ORDER BY	When present, the default frame becomes a running calculation up to the current row.
frame_clause	Override the default — look at a specific row range (e.g. trailing 3 rows).

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(200, 'Jennifer',  'Whalen',     4400.00, 101, 1),
(201, 'Michael',   'Hartstein', 13000.00, 100, 2),
(202, 'Pat',       'Fay',        6000.00, 201, 2),
(203, 'Susan',     'Mavris',     6500.00, 101, 4),
(204, 'Hermann',   'Baer',      10000.00, 101, 4),
(205, 'Shelley',   'Higgins',   12008.00, 101, 1),
(206, 'William',   'Gietz',      8300.00, 205, 1),
(100, 'Steven',    'King',      24000.00, NULL, 9),
(101, 'Neena',     'Kochhar',   17000.00, 100, 9),
(102, 'Lex',       'De Haan',   17000.00, 100, 9);

Example 1 — overall window

SQL

SELECT first_name,
       salary,
       SUM(salary)   OVER () AS total_payroll,
       AVG(salary)   OVER () AS avg_salary,
       COUNT(*)      OVER () AS headcount,
       MAX(salary)   OVER () AS highest,
       MIN(salary)   OVER () AS lowest
FROM   employees;

Every row gets the company-wide totals tacked on. No GROUP BY would let you keep the per-person rows alongside the same numbers.

Example 2 — partition by department

SQL

SELECT first_name,
       department_id,
       salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_payroll,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg
FROM   employees;

The diff_from_dept_avg column shows where each employee sits relative to their department's average — positive means they earn above average, negative below. That comparison would be awkward without window functions.

Example 3 — running total with ORDER BY

SQL

SELECT employee_id,
       first_name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM   employees
ORDER  BY employee_id;

Adding ORDER BY changes the default frame to "everything from the start of the partition up to and including this row" — so each row's SUM is a running total.

Example 4 — trailing window with a frame clause

SQL

SELECT employee_id,
       salary,
       AVG(salary) OVER (
           ORDER BY employee_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_3_avg
FROM   employees
ORDER  BY employee_id;

Here the window is "this row plus the two before it" — a 3-row trailing average, the kind of thing dashboards use for noisy time-series.

Frame clause cheat sheet

Clause	Meaning
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Default with ORDER BY — running calculation.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	The whole partition, regardless of ordering.
ROWS BETWEEN N PRECEDING AND CURRENT ROW	Trailing N+1 rows.
ROWS BETWEEN N PRECEDING AND N FOLLOWING	Centered window of 2N+1 rows.
RANGE BETWEEN ...	Same idea, but based on the ORDER BY column's value rather than row count.

The DISTINCT trap

⚠ DISTINCT isn't supported inside window aggregates. SUM(DISTINCT col) OVER (...) and friends are not legal SQL — the ALL keyword (the default) is the only choice. If you need a distinct count over a window, use a subquery or compute it differently.

Recap

Add OVER() to any of the five aggregates and they become window functions.
PARTITION BY resets the calculation per group.
ORDER BY turns the default frame into a running calculation.
A custom frame clause (ROWS BETWEEN …) gives you trailing/centred windows.
DISTINCT is not allowed inside window aggregates.