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.
SELECT id, total, SUM(total) OVER (ORDER BY id) AS running_total FROM invoices;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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 BYand no frame the frame isRANGE 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 writeROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfor strict row-by-row totals. - Without
ORDER BYthe frame is the whole partition, so the aggregate is the same on every row of the group. NULLs are ignored bySUM,AVG,MIN,MAXandCOUNT(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, soSUM(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 BYwhen you want fewer rows. The window form returns one row per input row.
Related
OVER(),PARTITION BY, window frames,SUM(),AVG(),FILTER- Lessons: Running totals, Window functions