Window functions

Is OVER a function in SQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

No. OVER is a clause, not a function. It attaches to a function and changes what that function returns.

The examples below read orders, one row per order with a customer_id and a total_amount, and the last one joins customers for the name. Both are below, 200 orders in this copy of the table.

That is why you cannot find documentation for a function called OVER. There is not one. In SUM(total_amount) OVER (), SUM() is the function. OVER () is the instruction that tells SUM() to compute across a window of rows and hand a value back to every row, instead of collapsing them into one.

PostgreSQL will tell you this itself, in one line, if you ask it directly.

Every result below was produced by running its query, and two of them sit in editors you can change and re-run. There are two exercises at the end of this page.

Schema · ecommerce2 tables? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

orders

idinteger

customer_id?integer

ordered_attimestamptz

statustext

total_amountnumeric

orders	customers	all_orders_total
200	70	126725.73

the tables every number below is drawn from, and the total OVER () reports

What happens if you call OVER on its own?

The query fails. PostgreSQL goes looking for a function named over, finds no such function, and says so. Ask it to select OVER () with nothing attached to it:

SELECT OVER ()
FROM orders;

PostgreSQL responds function over() does not exist

The database looked for a function by that name, because that is what the syntax asked for, and there is no such function to find. Try it, and change it:

SELECT OVER ()
FROM orders;

Attaching it to a plain column instead of a function does not work either:

SELECT total_amount OVER ()
FROM orders;

PostgreSQL responds syntax error at or near "OVER"

There is nothing there for the window to apply to.

What does OVER actually do to a function?

With empty parentheses, PostgreSQL computes the same number it computes without OVER, and hands that one number to every row instead of collapsing the rows into one. The shape of the result changes, and the number does not.

What you write inside those parentheses is a separate question, because that can change the number itself. Write SUM(total_amount) OVER (ORDER BY id) against this same table and PostgreSQL hands back a running total, 129.98 on the first order and 1128.98 on the second, rather than the 126725.73 that OVER () puts on every row. The same goes for PARTITION BY, further down this page. Empty parentheses are the place to start, because there the number stays put.

So run the same function twice, once without OVER and once with OVER (). Start with a plain aggregate over the orders table:

SELECT SUM(total_amount) AS total
FROM orders;

total
126725.73

200 orders in, one row out

Two hundred rows went in and one row came out. That is what an aggregate does: it collapses the rows it read.

Now the same SUM(total_amount), unchanged, with OVER () attached to it:

SELECT id,
       total_amount,
       SUM(total_amount) OVER () AS all_orders_total
FROM orders
ORDER BY id;

id	total_amount	all_orders_total
1	129.98	126725.73
2	999.00	126725.73
3	1199.00	126725.73
4	34.99	126725.73
5	649.00	126725.73
6	249.00	126725.73
7	799.00	126725.73
8	1999.00	126725.73

every order keeps its row, and carries the same total · 192 more rows not shown

The total is identical. The function did not change, and the number it computed did not change. What changed is the shape of the result: two hundred rows instead of one, each carrying the total alongside its own total_amount.

That is the whole idea. OVER is what makes a function a window function, and a window function reports without collapsing.

Try it: delete OVER () from the query below and run it again. The query stops working altogether:

PostgreSQL responds column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

Without the window, SUM() is a plain aggregate, and a plain aggregate cannot hand back id for every row. That refusal is the same point from the other side.

SELECT id,
       total_amount,
       SUM(total_amount) OVER () AS all_orders_total
FROM orders
ORDER BY id;

That experiment works because SUM() has both forms. It is an aggregate on its own and a window function with OVER attached, which is what makes it the clearest thing to demonstrate on.

Not every function does. ROW_NUMBER, LAG and LEAD have no plain form at all, so there is no version of them to run without OVER:

SELECT ROW_NUMBER()
FROM orders;

PostgreSQL responds window function row_number requires an OVER clause

Delete OVER from one of those and PostgreSQL does not fall back to anything, because there is nothing to fall back to. If you came here from a query like ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), that is the error you get, and it is the same answer as the rest of this page: the OVER is not an optional decoration on the function. It is the part that makes the function legal.

RANK is a near miss, and it is one of four. PostgreSQL 17 marks eleven names in its catalog as window functions, and for four of them it ships a second function beside the window one: RANK, DENSE_RANK, PERCENT_RANK and CUME_DIST. Each of those four second forms is a hypothetical-set aggregate you write as rank(value) WITHIN GROUP (ORDER BY column) and call with no OVER attached. Write any of the four with empty parentheses in a SELECT list and PostgreSQL still asks you for the window.

What goes inside the OVER parentheses?

Empty parentheses mean one window containing every row. Three optional parts narrow it:

PARTITION BY splits the rows into groups and restarts the calculation in each one.
ORDER BY, written inside OVER, sequences the rows within a window. Running totals need it. So do LAG and LEAD.
A frame clause is how you limit which rows around the current one are counted. PostgreSQL 17 accepts three ways to write one: ROWS, RANGE and GROUPS.

What does PARTITION BY do?

PARTITION BY restarts the calculation for each group, so a row carries its own group's value instead of the whole table's. Adding PARTITION BY gives each customer their own total:

SELECT c.name,
       o.total_amount,
       SUM(o.total_amount) OVER (PARTITION BY c.id) AS customer_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.name;

name	total_amount	customer_total
Alan Ward	799.00	1547.00
Alan Ward	299.00	1547.00
Alan Ward	449.00	1547.00
Alice Nguyen	129.98	4275.98
Alice Nguyen	249.00	4275.98
Alice Nguyen	1999.00	4275.98
Alice Nguyen	1099.00	4275.98
Alice Nguyen	799.00	4275.98

the total restarts per customer, and every order still has a row · 192 more rows not shown

Alan Ward's three orders each carry his total, not the whole table's. GROUP BY would have returned one row per customer and thrown the individual orders away. Aggregate window functions goes further into that contrast.

Where is OVER allowed?

Only in the SELECT list and in ORDER BY. Put one in WHERE and PostgreSQL refuses it:

SELECT id
FROM orders
WHERE SUM(total_amount) OVER () > 100;

PostgreSQL responds window functions are not allowed in WHERE

The same applies to GROUP BY and HAVING. The reason is ordering: PostgreSQL applies WHERE before it computes any window function, so when the filter runs the value does not exist yet. To filter on one, compute it in a CTE or subquery first, then filter the outer query.

Every error on this page is PostgreSQL's own.

GROUP BY or OVER?

GROUP BY collapses the rows into one per group. OVER keeps every row and puts the group's value beside it. That is the whole difference, and it decides which one you want.

	GROUP BY	OVER (PARTITION BY)
Rows returned	One per group	One per input row
Individual rows	Discarded	Kept
Join needed to get them back	Yes	No

The PARTITION BY query further up is the right-hand column. Its join brings in the customer. What it never does is join back to a grouped result to recover the individual orders, because it never threw them away.

What happens if you call OVER on its own?

What does OVER actually do to a function?

What goes inside the OVER parentheses?

What does PARTITION BY do?

Where is OVER allowed?

GROUP BY or OVER?

Practice: write OVER and PARTITION BY yourself