Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL OVER() clause

Reference for the SQL OVER() clause: turn aggregate and ranking functions into window functions with PARTITION BY, ORDER BY and frames, without collapsing rows.

The OVER clause turns a function into a window function. A normal aggregate such as SUM() collapses a group of rows into one row. With OVER, the same function is calculated across a set of related rows (the window), but every input row stays in the result and gets its own value.

Inside the parentheses you can describe the window with up to three parts: PARTITION BY splits rows into independent groups, ORDER BY sorts the rows inside each group, and a frame clause narrows the rows that count for the current row. All three are optional. An empty OVER () means "the whole result set".

SQL
SELECT first_name, salary,
       AVG(salary) OVER () AS company_average
FROM employees;

Try it

SQL An aggregate next to the rows it summarizes ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Partitions, ordering and a running total ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
function_name(arguments) OVER (
  [ PARTITION BY expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
  [ { ROWS | RANGE | GROUPS } frame_extent ]
)

function_name(arguments) OVER window_name       -- refers to a WINDOW clause
Part Description
function_name An aggregate (SUM, AVG, COUNT, MIN, MAX) or a window-only function (ROW_NUMBER, RANK, LAG, ...).
PARTITION BY Splits rows into groups. The function restarts in each partition. See PARTITION BY.
ORDER BY Orders rows inside each partition. Required for ranking and offset functions, and it changes the default frame of aggregates.
frame clause Chooses which rows around the current row are included. See window frames.
window_name A name defined in a WINDOW clause.

Returns: one value per input row, of the function's usual type.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
Window functions 3.25+ 8.4+ 8.0+ 2005+ (frames since 2012) 8i+
GROUPS frame unit 3.28+ 11+ No No No
EXCLUDE in frames 3.28+ 11+ No No No
NULLS FIRST / LAST 3.30+ Yes No No Yes
FILTER (WHERE ...) on aggregates 3.30+ Yes No No No
Named WINDOW clause Yes Yes Yes 2022+ Not in older releases
SQL
-- SQL Server before 2022: no WINDOW clause, so repeat the definition
SELECT salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

Usage notes

  • Window functions run after WHERE, GROUP BY and HAVING. They see the rows that survived filtering and grouping. You cannot use one in WHERE, GROUP BY or HAVING; wrap the query in a subquery or CTE and filter the outer one.
  • They may appear only in SELECT and ORDER BY. That is where the standard allows them.
  • ORDER BY inside OVER is independent of the final ORDER BY. The one in OVER defines the calculation order; the outer one sorts the output.
  • An aggregate with ORDER BY in OVER becomes cumulative. Without a frame clause, the default is from the partition start up to the current row, including peers with equal sort values. That makes SUM(x) OVER (ORDER BY d) a running total.
  • Without ORDER BY the frame is the whole partition, so each row gets the same value.
  • Window functions do not change the row count. If you need one row per group, use GROUP BY instead.
  • Several windows in one query are fine. The database may sort once per distinct window, so reuse the same definition when you can.
  • Ties in the ORDER BY make ROW_NUMBER() arbitrary among the equal rows. Add a unique column as a tie-breaker.