SUM() OVER
SQL · Window Functions
Theory
SUM(column) OVER (...) adds values across the window and puts the result on every row, without collapsing rows. What it adds depends on what you put inside OVER. With only PARTITION BY region, each row gets its region's total. With ORDER BY month (and optionally PARTITION BY), each row gets a running total from the first month up to the current one, restarting for every region.
This is the standard way to build cumulative figures, such as year-to-date sales or a bank balance from a list of transactions. Remember the default frame with ORDER BY is RANGE, so rows with the same ORDER BY value are added together in one step; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a strict row-by-row running total. You can also combine both forms: revenue * 100.0 / SUM(revenue) OVER (PARTITION BY region) gives each month's share of its region.
Why we use it
Running totals and 'share of group' figures are some of the most requested report columns. SUM() OVER computes them in one scan, where the old way needed a correlated subquery that re-added all earlier rows for every row.
When to use it
Use SUM() OVER for running totals, year-to-date figures and per-group totals next to detail rows. Use plain SUM with GROUP BY when you only need one total per group.
How it works
- Rows split by PARTITION BY
- ORDER BY inside OVER? Yes: Frame = first row to current row. No: Frame = whole partition.
- Add the non-NULL values in the frame
- Write the total on the current row
- New partition starts? Yes: Total restarts from zero. No: Keep accumulating.
- Running or group total on every row
Syntax
SUM(column) OVER (ORDER BY col)Example
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;Key points
- With ORDER BY and no frame, becomes a running total
- With PARTITION BY only, becomes a per-group total on every row
- Default frame is RANGE: equal ORDER BY values are added together
- Use ROWS UNBOUNDED PRECEDING for a strict row-by-row total
- Ignores NULLs, like the aggregate SUM