Aggregate and Window Functions

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

Aggregate functions collapse a set of rows into a single summary value, typically in combination with GROUP BY. Window functions, introduced by the SQL:2003 standard, compute a value for every input row while still having access to a set of related rows — the row’s "window" — without collapsing the result set. This page covers both, and shows how the same aggregate functions can be reused as window functions via the OVER clause. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines aggregate and window functions.

The examples below assume a table of orders:

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    region       VARCHAR(20) NOT NULL,
    order_date   DATE NOT NULL,
    amount       NUMERIC(10, 2) NOT NULL,
    discount_amount NUMERIC(10, 2)
);

Aggregate Functions

Aggregate functions take a collection of rows (the whole table, or one group produced by GROUP BY) and return a single value. Except for COUNT(*), all aggregate functions ignore NULL values in their input.

COUNT

COUNT returns the number of rows in a group. It has three forms:

COUNT(*) — counts every row in the group, including rows that contain NULL values in any column.
COUNT(column) — counts only the rows where column is not NULL.
COUNT(DISTINCT column) — counts the number of distinct, non-NULL values of column.

SELECT
    COUNT(*)                          AS total_rows,
    COUNT(discount_amount)            AS rows_with_discount,
    COUNT(DISTINCT customer_id)       AS distinct_customers
FROM orders;

SUM

SUM(expression) returns the total of a numeric expression across the rows in a group, ignoring NULL values. It returns NULL if every value in the group is NULL or the group is empty.

SELECT region, SUM(amount) AS region_total
FROM orders
GROUP BY region;

AVG

AVG(expression) returns the arithmetic mean of a numeric expression across the rows in a group, ignoring NULL values.

SELECT region, AVG(amount) AS region_average
FROM orders
GROUP BY region;

MIN

MIN(expression) returns the smallest value of an expression across the rows in a group. It works on numeric, character, and date/time types, and ignores NULL values.

SELECT region, MIN(order_date) AS first_order_date
FROM orders
GROUP BY region;

MAX

MAX(expression) returns the largest value of an expression across the rows in a group. Like MIN, it works on numeric, character, and date/time types, and ignores NULL values.

SELECT region, MAX(amount) AS largest_order
FROM orders
GROUP BY region;

Window Functions

A window function computes a value for each row using a set of rows related to that row, but — unlike GROUP BY — it does not reduce the number of rows returned. Every window function call requires an OVER clause, which defines the window: the rows visible to the function for each row of the result.

The OVER Clause

The general form is:

function_name(...) OVER (
    [ PARTITION BY partition_expression [, ...] ]
    [ ORDER BY sort_expression [ASC | DESC] [, ...] ]
)

PARTITION BY divides the rows into independent groups (partitions). The window function is evaluated separately within each partition, restarting for every new partition — much like GROUP BY, except rows are not collapsed. Omitting PARTITION BY treats the entire result set as a single partition.
ORDER BY defines the logical order of rows within each partition. It determines row order for order-sensitive functions such as ROW_NUMBER, RANK, and LAG/LEAD, and, for aggregate functions used as window functions, it also determines the running/cumulative frame (see the SUM() OVER example below). Omitting ORDER BY means there is no defined row order within the partition.

SELECT
    customer_id,
    region,
    amount,
    order_date
FROM orders
ORDER BY region, customer_id;

The subsections below apply this OVER (PARTITION BY … ORDER BY …) clause to specific functions.

ROW_NUMBER

ROW_NUMBER() returns a unique, sequential integer for each row within its partition, starting at 1 and following the order given by ORDER BY. It takes no parameters. Because it is strictly sequential, ties in the ORDER BY expression are broken arbitrarily — no two rows in the same partition ever get the same number.

SELECT
    customer_id,
    region,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS row_num
FROM orders;

RANK

RANK() returns the position of each row within its partition, according to the order given by ORDER BY. Rows with equal values in the ORDER BY expression receive the same rank, and the next rank after a tie is skipped by the number of tied rows (e.g. after two rows tied at rank 1, the next row gets rank 3).

SELECT
    customer_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS amount_rank
FROM orders;

DENSE_RANK

DENSE_RANK() behaves like RANK(), assigning the same rank to tied rows, but it never skips a rank value: the rank after a tie is always one greater than the previous rank, regardless of how many rows were tied.

SELECT
    customer_id,
    region,
    amount,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS amount_dense_rank
FROM orders;

LAG and LEAD

LAG and LEAD look at a row that comes before (LAG) or after (LEAD) the current row, within the same partition, according to the order given by ORDER BY.

LAG(expression [, offset [, default_value]]) OVER (...)
LEAD(expression [, offset [, default_value]]) OVER (...)

expression — the column or expression to read from the offset row.
offset — how many rows before (LAG) or after (LEAD) the current row to look at. Defaults to 1 when omitted.
default_value — the value returned when the offset row falls outside the partition (e.g. LAG on the first row, or LEAD on the last row). Defaults to NULL when omitted.

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount,
    LEAD(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS next_order_amount
FROM orders;

NTILE

NTILE(n) divides the rows of each partition, in the order given by ORDER BY, into n roughly equal-sized buckets, and returns the bucket number (from 1 to n) for each row. When the number of rows in a partition is not evenly divisible by n, the earlier buckets receive one extra row each.

SELECT
    customer_id,
    amount,
    NTILE(4) OVER (
        ORDER BY amount DESC
    ) AS amount_quartile
FROM orders;

Aggregate Functions as Window Functions

Any aggregate function — COUNT, SUM, AVG, MIN, MAX — can be used as a window function simply by adding an OVER clause instead of a GROUP BY clause. This preserves one output row per input row while still producing group-level or running totals alongside the detail data.

SELECT
    customer_id,
    region,
    order_date,
    amount,
    -- total for the whole partition (no ORDER BY inside OVER)
    SUM(amount) OVER (
        PARTITION BY region
    ) AS region_total,
    -- running total within each customer, ordered by date
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS running_customer_total,
    AVG(amount) OVER (
        PARTITION BY region
    ) AS region_average
FROM orders;

Bibliography

Kline, Kevin; Kline, Daniel; Hunt, Brand. SQL in a Nutshell. O’Reilly Media.
ISO/IEC 9075-2:2003, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).