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 ()

-- Calculate the running total of sales by order date and its percentage of the grand total.
SELECT
    order_date,
    sales_amount,
    SUM(sales_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    SUM(sales_amount) OVER () AS grand_total,
    ROUND(
        sales_amount * 100.0 / SUM(sales_amount) OVER (), 1
    ) AS percentage
FROM daily_sales
ORDER BY order_date;

-- Calculate the 3-day moving average of sales.
SELECT
    sales_date,
    sales_amount,
    AVG(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3days
FROM daily_sales
ORDER BY sales_date;

-- Calculate the running total of salary within each department.
SELECT
    department,
    employee,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS dept_running_total
FROM employees
ORDER BY department, salary DESC;

-- Running total and percentage.
order_date  | sales_amount | running_total | grand_total | percentage
------------+--------------+---------------+-------------+-----------
2024-01-01  | 120000       | 120000        | 435000      | 27.6
2024-01-02  | 85000        | 205000        | 435000      | 19.5
2024-01-03  | 230000       | 435000        | 435000      | 52.9

-- 3-day moving average.
sales_date  | sales_amount | moving_avg_3days
------------+--------------+-----------------
2024-01-01  | 120000       | 120000.0
2024-01-02  | 85000        | 102500.0
2024-01-03  | 230000       | 145000.0
2024-01-04  | 98000        | 137666.7

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

-- Calculate the running total of sales (compatible with all major databases).
SELECT
    order_date,
    sales_amount,
    SUM(sales_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.

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