SUM / AVG (Window)

Running total (from the first row to the current row).

SUM(column) OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Moving average over the last N rows.

AVG(column) OVER (
    ORDER BY order_column
    ROWS BETWEEN N PRECEDING AND CURRENT ROW
)

Grand total (no frame clause, no PARTITION BY).

SUM(column) OVER ()

Frame clause	Description
UNBOUNDED PRECEDING	The first row of the window (or partition).
CURRENT ROW	The current row.
UNBOUNDED FOLLOWING	The last row of the window (or partition).
N PRECEDING	N rows before the current row.
N FOLLOWING	N rows after the current row.

The following examples use the daily_sales table.

Calculate the running total of sales by order date and its percentage of the grand total.

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    SUM(amount) OVER () AS grand_total,
    ROUND(
        amount * 100.0 / SUM(amount) OVER (), 1
    ) AS pct_of_total
FROM daily_sales
ORDER BY order_date;

+------------+--------+---------------+-------------+--------------+
| order_date | amount | running_total | grand_total | pct_of_total |
+------------+--------+---------------+-------------+--------------+
| 2024-01-01 | 120000 |        120000 |      533000 |         22.5 |
| 2024-01-02 |  85000 |        205000 |      533000 |         15.9 |
| 2024-01-03 | 230000 |        435000 |      533000 |         43.2 |
| 2024-01-04 |  98000 |        533000 |      533000 |         18.4 |
+------------+--------+---------------+-------------+--------------+
4 rows in set

Calculate the 3-day moving average of sales.

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM daily_sales
ORDER BY order_date;

+------------+--------+---------------+
| order_date | amount | moving_avg_3d |
+------------+--------+---------------+
| 2024-01-01 | 120000 |   120000.0000 |
| 2024-01-02 |  85000 |   102500.0000 |
| 2024-01-03 | 230000 |   145000.0000 |
| 2024-01-04 |  98000 |   137666.6667 |
+------------+--------+---------------+
4 rows in set

Calculate the 2-day rolling sum of sales.

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS moving_sum_2d
FROM daily_sales
ORDER BY order_date;

+------------+--------+---------------+
| order_date | amount | moving_sum_2d |
+------------+--------+---------------+
| 2024-01-01 | 120000 |        120000 |
| 2024-01-02 |  85000 |        205000 |
| 2024-01-03 | 230000 |        315000 |
| 2024-01-04 |  98000 |        328000 |
+------------+--------+---------------+
4 rows in set

Window function syntax for SUM() and AVG() with frame clauses is supported in MySQL (8.0+), PostgreSQL, Oracle, SQL Server, and SQLite (3.25+).

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales
ORDER BY order_date;

SUM and AVG as window functions add aggregate values to each row without reducing the number of rows. The key is to use the frame clause to specify which rows are included in the calculation.

If you specify ORDER BY but omit the frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Rows with the same ORDER BY value are all treated as the "current row," which can produce unexpected results. Make it a habit to always specify ROWS BETWEEN explicitly. When in doubt, use ROWS.

For the basic window function syntax, see OVER / PARTITION BY. For referencing previous or next rows, see LAG / LEAD.