Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL aggregate functions as window functions

Use SUM, AVG, COUNT, MIN and MAX with OVER() in SQL: totals beside detail rows, running totals, moving averages, percent of total and the default frame.

Any ordinary aggregate (SUM, AVG, COUNT, MIN, MAX, and in most databases GROUP_CONCAT/STRING_AGG) becomes a window function when you add OVER (...). The aggregate is then computed over the rows of the window, but the rows themselves are not collapsed. That lets you show a total next to the detail it summarizes, or calculate running totals and moving averages.

What the window contains depends on three things: PARTITION BY (which group), ORDER BY (the order, which makes the aggregate cumulative) and an optional frame (which neighbouring rows count). Understanding the default frame is the key to getting the right numbers.

SQL
SELECT id, total,
       SUM(total) OVER (ORDER BY id) AS running_total
FROM invoices;

Try it

SQL Total, share of total and running total ยท database: shop

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

SQL Group totals and a moving average ยท database: company

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

SQL The default frame includes peers (equal values) ยท database: school

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

Syntax

SQL
aggregate_function([DISTINCT] expression) OVER (
  [ PARTITION BY ... ]
  [ ORDER BY ... [ frame ] ]
)

aggregate_function(expression) FILTER (WHERE condition) OVER (...)
Window shape Meaning
OVER () Every row sees the aggregate of the whole result.
OVER (PARTITION BY g) Every row sees the aggregate of its group.
OVER (ORDER BY x) Cumulative: from the first row up to the current row and its peers.
OVER (ORDER BY x ROWS BETWEEN a AND b) Exactly the rows between the two boundaries, such as a moving window.

Returns: the aggregate's normal result for each row. SUM, AVG, MIN and MAX are NULL for a frame with no non-NULL values; COUNT is 0.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
Aggregates with OVER 3.25+ Yes 8.0+ Yes Yes
FILTER clause 3.30+ Yes No No No
ROWS frames Yes Yes Yes 2012+ Yes
Default frame uses RANGE Yes Yes Yes Yes Yes
SQL
-- SQL Server, MySQL, Oracle: no FILTER, so use CASE inside the aggregate
SELECT SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id)
FROM orders;

Usage notes

  • Default frame trap. With ORDER BY and no frame the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Rows with the same sort value are peers and all see the same cumulative total (see the third example). Add a unique tie-breaker or write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for strict row-by-row totals.
  • Without ORDER BY the frame is the whole partition, so the aggregate is the same on every row of the group.
  • NULLs are ignored by SUM, AVG, MIN, MAX and COUNT(column), exactly as in normal aggregates. COUNT(*) counts every row.
  • Percent of total needs 100.0 * or a cast, because integer division truncates in most databases.
  • Can be combined with GROUP BY. The window then runs over the grouped result, so SUM(SUM(x)) OVER () gives a grand total beside each group's sum.
  • Not allowed in WHERE. Filter on a window result in an outer query.
  • Performance: a running total over a large partition is computed in one pass, far cheaper than a correlated subquery that rescans the table for every row.
  • Choose GROUP BY when you want fewer rows. The window form returns one row per input row.