SQL SUM() OVER — Guide to Running Totals and Cumulative Sums

SUM() OVER calculates running totals and cumulative sums without collapsing rows. It is a window function — it adds a computed column to each row based on a sliding window of related rows. Unlike plain SUM with GROUP BY, every original row stays in the result set.

This guide focuses on running totals, moving sums, and the frame specifications that control which rows participate in each calculation.

SUM() vs SUM() OVER

The core distinction is about row count. SUM(amount) ... GROUP BY department collapses rows and returns one row per department. SUM(amount) OVER(PARTITION BY department) keeps all rows and appends the department total as a new column.

A side-by-side example makes this concrete. Given a table of three employees across two departments:

-- GROUP BY: 2 rows out SELECT department, SUM(salary) AS dept_total FROM employees GROUP BY department; -- SUM() OVER: 3 rows out (all original rows preserved) SELECT name, department, salary, SUM(salary) OVER(PARTITION BY department) AS dept_total FROM employees;

Use GROUP BY when you need a summary table. Use SUM() OVER when you need the detail rows and the summary at the same time — for example, calculating each employee's percentage of the department total.

Running Totals

Add ORDER BY inside the OVER clause and SUM becomes a running total — each row shows the cumulative sum of all rows up to and including itself:

SELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS running_total FROM daily_sales ORDER BY sale_date; -- Result: -- sale_date | amount | running_total -- 2024-01-01 | 100 | 100 -- 2024-01-02 | 200 | 300 -- 2024-01-03 | 150 | 450 -- 2024-01-04 | 300 | 750 -- 2024-01-05 | 250 | 1,000

The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This matters because RANGE groups rows by value, not by position. If two rows share the same sale_date, both get the same running total — the sum "jumps" to include both at once.

For deterministic, row-by-row running totals, use ROWS instead of the default RANGE:

SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Or add a tiebreaker column to the ORDER BY — a primary key works:

SUM(amount) OVER(ORDER BY sale_date, sale_id)

Partitioned Running Totals

Adding PARTITION BY restarts the running total for each group. A year-to-date revenue report by department:

SELECT department, sale_month, monthly_revenue, SUM(monthly_revenue) OVER( PARTITION BY department ORDER BY sale_month ) AS ytd_revenue FROM monthly_dept_sales WHERE sale_year = 2024 ORDER BY department, sale_month;

The percentage of partition total pattern uses SUM() OVER without ORDER BY to get the partition-wide total, then divides:

SELECT department, employee_name, salary, ROUND( 100.0 * salary / SUM(salary) OVER(PARTITION BY department), 1 ) AS pct_of_dept FROM employees;

Without ORDER BY, the window covers the entire partition — every row in the department sees the same total, which is exactly what you need for a percentage calculation.

Frame Specifications

Frame specifications control exactly which rows participate in each SUM calculation. The syntax is:

ROWS BETWEEN <start> AND <end>

Where <start> and <end> can be UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, or UNBOUNDED FOLLOWING.

The most common frame specifications:

• UNBOUNDED PRECEDING TO CURRENT ROW — running total (the default with ORDER BY)
• N PRECEDING TO CURRENT ROW — moving sum (e.g., last 7 days)
• N PRECEDING TO N FOLLOWING — centered window (e.g., smoothing)
• UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING — partition total (same as omitting ORDER BY)

Practical Patterns

Moving 7-day sum for sales analysis:

SELECT sale_date, daily_revenue, SUM(daily_revenue) OVER( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS seven_day_sum FROM daily_sales;

Inventory level tracking — SUM of quantity changes gives the current stock level at each point in time:

SELECT txn_date, txn_type, qty_change, SUM(qty_change) OVER( ORDER BY txn_date, txn_id ) AS stock_level FROM inventory_transactions WHERE product_id = 'SKU-001';

Account balance — deposits as positive, withdrawals as negative:

SELECT txn_date, txn_type, CASE WHEN txn_type = 'deposit' THEN amount ELSE -amount END AS net_amount, SUM(CASE WHEN txn_type = 'deposit' THEN amount ELSE -amount END) OVER(ORDER BY txn_date, txn_id) AS balance FROM account_transactions WHERE account_id = 42;

Percentage of total — each row's share of the overall sum:

SELECT product_name, revenue, ROUND(100.0 * revenue / SUM(revenue) OVER(), 1) AS pct_of_total FROM product_sales;

Running totals and cumulative sums only make sense when the underlying metric is consistently defined. If "revenue" means different things in different tables, a running total combines incompatible numbers. A data catalog with a business glossary — or a semantic layer — ensures that a SUM(revenue) OVER query uses a canonical, governed definition of revenue.

Combining with Other Window Functions

SUM() OVER works alongside ROW_NUMBER, RANK, LAG, and COUNT() OVER in the same query. A stock price analysis that combines several window functions:

SELECT trading_date, closing_price, SUM(volume) OVER( ORDER BY trading_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) AS five_day_volume, ROUND(AVG(closing_price) OVER( ORDER BY trading_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW ), 2) AS twenty_day_ma, closing_price - LAG(closing_price) OVER( ORDER BY trading_date ) AS daily_change FROM stock_prices WHERE symbol = 'AAPL' ORDER BY trading_date;

A customer lifetime value calculation using a CTE with SUM() OVER:

WITH customer_orders AS ( SELECT customer_id, order_date, order_total, SUM(order_total) OVER( PARTITION BY customer_id ORDER BY order_date ) AS cumulative_value, ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY order_date ) AS order_number FROM orders ) SELECT customer_id, MAX(cumulative_value) AS lifetime_value, COUNT(*) AS total_orders, ROUND(AVG(order_total), 2) AS avg_order FROM customer_orders GROUP BY customer_id HAVING COUNT(*) >= 3;

For deeper coverage of ranking functions and PARTITION BY patterns, see the dedicated article.

Performance

Index strategy. The ideal covering index for a window function query includes the partition columns, then the ORDER BY column, then the summed column — in that order:

CREATE INDEX idx_sales_dept_date_amount ON sales (department, sale_date, amount);

This lets the database read rows in the right order without sorting, and it covers the query without visiting the heap.

ROWS vs RANGE performance. ROWS frame specifications are faster than RANGE because the database can use a simple sliding window — add one value, subtract one value. RANGE must group duplicate ORDER BY values, which adds overhead.

Large partitions. Window function calculations buffer partition data in memory. If one partition has 10 million rows, the database needs memory for all of them. When partitions are very large, consider materializing intermediate results in a CTE or temp table.

Window function vs self-join. Before window functions existed, running totals required a self-join — SUM(b.amount) WHERE b.date <= a.date. This is O(n^2). The window function approach is O(n log n) or better. If you encounter running total queries written as self-joins, rewriting them as SUM() OVER typically produces a 10x or greater speed improvement.