SQL OVER

What is the SQL OVER clause?

Applies a window (partition and order) to an aggregate or analytic function so each row can reference related rows without collapsing the result set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OVER: PostgreSQL, MySQL 8.0+, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite 3.25+, DB2, MariaDB 10.2+

SQL OVER Full Explanation

The OVER clause turns an ordinary aggregate, ranking, or analytic function into a window function. Instead of returning one summary row, the function is evaluated for every input row across a logical window defined by PARTITION BY, ORDER BY, and an optional frame (ROWS or RANGE). The clause lets you calculate running totals, moving averages, rankings, percentiles, and other advanced analytics while still keeping the original granular rows. If PARTITION BY is omitted, the entire result set is treated as a single partition. If ORDER BY is omitted, the window is unordered, so the frame defaults to all rows in the partition. Frame specifications such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW further narrow the set of rows used in each calculation. The OVER clause does not change the number of rows returned—one output row is produced for every input row—nor does it allow filtering; that must be done with WHERE or HAVING. Because window functions are evaluated after WHERE, GROUP BY, and HAVING but before ORDER BY, they cannot be referenced in those earlier clauses in most databases.

SQL OVER Syntax

<aggregate_or_analytic_function>() OVER (
    [PARTITION BY expression_list]
    [ORDER BY expression_list]
    [ROWS | RANGE frame_spec]
);

SQL OVER Parameters

PARTITION BY (expression list) - divides rows into independent windows.
ORDER BY (expression list) - defines ordering inside each partition.
ROWS/RANGE frame_spec (window frame) - sets start and end relative to current row.

Example Queries Using SQL OVER

-- Running total of sales per customer ordered by purchase date
SELECT
  customer_id,
  purchase_date,
  amount,
  SUM(amount) OVER (
      PARTITION BY customer_id
      ORDER BY purchase_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

-- Rank products by revenue within each category
SELECT
  category,
  product_id,
  revenue,
  RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
FROM product_revenue;

-- Compare each employee's salary with the departmental average
SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

Expected Output Using SQL OVER

Each query returns the same number of rows as its source table
Additional columns hold the calculated running total, rank, or departmental average for the corresponding row

Use Cases with SQL OVER

Generating running totals, cumulative sums, moving averages
Ranking rows (RANK, DENSE_RANK, ROW_NUMBER)
Calculating percentiles or NTILE buckets
Comparing each row to partition level statistics such as averages or maximums
Flagging first or last value within a group without subqueries
Replacing self joins and correlated subqueries for performance and clarity

Common Mistakes with SQL OVER

Forgetting ORDER BY inside OVER when calculating running totals, which causes the window to be unordered and results in global totals
Using window function aliases in WHERE, GROUP BY, or HAVING clauses; they are not yet available at that stage
Expecting OVER to filter or group rows; it only adds computed values while preserving all rows
Mixing ROWS and RANGE frames incorrectly (e.g., RANGE with non-numeric order columns in databases that do not support it)
Omitting frame specification and assuming a moving window instead of the default cumulative frame

First Introduced In

SQL:2003 standard

Frequently Asked Questions

What is the difference between OVER and GROUP BY?

GROUP BY collapses rows into aggregates, returning one row per group. OVER keeps all original rows and adds a computed value for each.

Do I always need an ORDER BY inside OVER?

No, but ORDER BY is required for calculations that depend on row order such as running totals, moving averages, or rankings.

How do I filter on a window function result?

Wrap the query using the window function in a subquery or common table expression, then apply the filter in an outer WHERE clause.

Are window functions slower than joins?

Usually window functions are faster because the database can compute them in a single pass without materializing joins, but performance depends on indexes, partition size, and order columns.