OVER / PARTITION BY

Window functions provide a way to perform aggregation and ranking without collapsing rows into groups. The OVER() clause defines the window (the set of rows to process), PARTITION BY divides rows into groups, and ORDER BY specifies the sort order within each group.

Syntax

-- Basic syntax for a window function.
window_function() OVER (
    [PARTITION BY partition_column]
    [ORDER BY sort_column ASC|DESC]
    [frame_clause]
)

-- Frame clause: specifies the range of rows relative to the current row.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- From the first row to the current row
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW          -- From 2 rows before to the current row
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- From the current row to the last row

Syntax Reference

Syntax	Description
OVER()	Treats all rows as a single window. Adds an aggregate value for the entire result set to each row, without a GROUP BY.
PARTITION BY column	Divides the window into groups based on the specified column. Unlike GROUP BY, the number of rows is not reduced.
ORDER BY column	Specifies the sort order within the window. Required for order-dependent calculations such as running totals.
ROWS BETWEEN ... AND ...	Specifies the frame — the range of rows to include — relative to the current row.

Sample Code

-- Add the average salary per department to every row (row count is not reduced).
SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

-- Calculate the running total of sales ordered by order date (using a frame clause).
SELECT
    order_date,
    sales_amount,
    SUM(sales_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY order_date;

-- Add the maximum and minimum salary per department to every row.
SELECT
    employee_name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max_salary,
    MIN(salary) OVER (PARTITION BY department) AS dept_min_salary
FROM employees;

Result

-- Result with the department average salary added to each row.
employee_name  | department  | salary  | dept_avg_salary
---------------+-------------+---------+----------------
Hanako Suzuki  | Engineering | 620000  | 505000
Saburo Sato    | Engineering | 390000  | 505000
Taro Yamada    | Sales       | 480000  | 400000
Megumi Tanaka  | Sales       | 320000  | 400000

-- Running total of sales.
order_date  | sales_amount | running_total
------------+--------------+--------------
2024-01-05  | 120000       | 120000
2024-01-12  | 85000        | 205000
2024-01-20  | 230000       | 435000

Database Compatibility

This syntax is supported across all major databases: MySQL (8.0+), PostgreSQL, Oracle, SQL Server, and SQLite (3.25+).

-- Add the average salary per department to each row (compatible with all major databases).
SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

Notes

The key advantage of window functions is that unlike GROUP BY, they add aggregate values to each row without reducing the row count. This makes it straightforward to write queries such as "show each row alongside the department total for comparison."

If you specify only ORDER BY and omit the frame clause, the default frame is "from the first row to the current row (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)." For running total calculations, it is better to explicitly specify ROWS to get the intended result.

For ranking with window functions, see ROW_NUMBER / RANK / DENSE_RANK. For running totals and moving averages, see SUM / AVG (Window).

If you find any errors or copyright issues, please contact us.

Home

SQL Dictionary