OVER / PARTITION BY

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	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.

The following examples use the employees table.

Add the average salary per department to every row (row count is not reduced).

sample_over_partition_by.sql

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

+------------------+------------+--------+-----------------+
| name             | department | salary | dept_avg_salary |
+------------------+------------+--------+-----------------+
| Kogami Shinya    | Enforcer   | 480000 |    400000.0000  |
| Masaoka Tomomi   | Enforcer   | 320000 |    400000.0000  |
| Tsunemori Akane  | Inspector  | 620000 |    505000.0000  |
| Ginoza Nobuchika | Inspector  | 390000 |    505000.0000  |
+------------------+------------+--------+-----------------+
4 rows in set

Calculate the running total of salaries ordered by salary descending (using a frame clause).

sample_over_partition_by.sql

SELECT
    name,
    salary,
    SUM(salary) OVER (
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees
ORDER BY salary DESC;

+------------------+--------+---------------+
| name             | salary | running_total |
+------------------+--------+---------------+
| Tsunemori Akane  | 620000 |        620000 |
| Kogami Shinya    | 480000 |       1100000 |
| Ginoza Nobuchika | 390000 |       1490000 |
| Masaoka Tomomi   | 320000 |       1810000 |
+------------------+--------+---------------+
4 rows in set

Add the maximum and minimum salary per department to every row.

sample_over_partition_by.sql

SELECT
    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;

+------------------+------------+--------+-----------------+-----------------+
| name             | department | salary | dept_max_salary | dept_min_salary |
+------------------+------------+--------+-----------------+-----------------+
| Kogami Shinya    | Enforcer   | 480000 |          480000 |          320000 |
| Tsunemori Akane  | Inspector  | 620000 |          620000 |          390000 |
| Ginoza Nobuchika | Inspector  | 390000 |          620000 |          390000 |
| Masaoka Tomomi   | Enforcer   | 320000 |          480000 |          320000 |
+------------------+------------+--------+-----------------+-----------------+
4 rows in set

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

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

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).