---
title: "OVER clause"
description: "The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set."
canonical: "https://motherduck.com/glossary/over-clause/"
related:
  - title: "Window functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "EXPLAIN | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/explain/"
---

# OVER clause

> The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.

## Overview
The `OVER` clause is what distinguishes a window function from a regular aggregate function. Adding `OVER (...)` after a function like `SUM()`, `AVG()`, or `ROW_NUMBER()` tells the engine to compute the value per row over a defined "window" of related rows, rather than collapsing all rows into one.

```sql
SELECT
  order_id,
  customer_id,
  revenue,
  SUM(revenue) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;
```

The `OVER` clause has three optional pieces:
1. `PARTITION BY` — which rows belong together.
2. `ORDER BY` — how rows are ordered within a partition (required for ranking functions and frames).
3. A frame clause (`ROWS`/`RANGE`/`GROUPS BETWEEN ...`) — which rows within the ordered partition are included.

An empty `OVER ()` applies the function across the entire result set as one partition.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Named windows
When several window functions in a query share the same partition/order/frame, standard SQL lets you define a named window once with a `WINDOW` clause and reference it by name:

```sql
SELECT
  customer_id, order_date, revenue,
  SUM(revenue) OVER w AS running_total,
  AVG(revenue) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);
```

## DuckDB specifics
DuckDB supports the full `OVER` clause syntax, named `WINDOW` definitions, and the `EXCLUDE` frame modifier. It also supports `QUALIFY`, which filters rows based on a window function's output — the natural complement to `OVER`, since `WHERE` cannot reference window function results directly:

```sql
SELECT customer_id, revenue, SUM(revenue) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
QUALIFY running_total > 10000;
```