---
title: "SQL SUM() OVER - Guide to Running Totals and Cumulative Sums | Dawiso"
description: "SQL SUM() OVER calculates running totals and cumulative sums without collapsing rows. Covers frame specifications, partitioned sums, and moving windows."
url: "https://www.dawiso.com/glossary/sql-sum--over-complete-guide-to-window-functions-and-running-totals"
language: "en"
---

February 20, 2026

sql sum overwindow functionsrunning totalscumulative analysis

# 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](https://www.dawiso.com/glossary/sql-sum-avg-min-max-complete-guide-to-sql-aggregate-functions), 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.

TL;DR

**SUM() OVER** adds a cumulative or windowed sum column to your result set. With ORDER BY, it computes a running total. With [PARTITION BY](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning), it restarts the running total for each group. Frame specifications (`ROWS BETWEEN N PRECEDING AND CURRENT ROW`) create moving windows for calculations like 7-day sums. The default frame with ORDER BY is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, which includes all rows with the same ORDER BY value - not just the physical rows before the current one.

## 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](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning) 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)

The default window frame in SQL is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is specified. This means rows with duplicate ORDER BY values all see the same running total. Use ROWS instead of RANGE for deterministic, row-by-row accumulation.

\- PostgreSQL Documentation, [Window Function Calls](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)

## 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](https://www.dawiso.com/glossary/data-catalog) with a business glossary - or a [semantic layer](https://www.dawiso.com/glossary/semantic-layer) - ensures that a `SUM(revenue) OVER` query uses a canonical, governed definition of revenue.

Window functions are one of the most powerful features of SQL. They allow calculations across sets of rows related to the current row - running totals, rankings, moving averages - without the need for self-joins or correlated subqueries.

\- Markus Winand, [Use The Index, Luke - Window Functions](https://use-the-index-luke.com/sql/partial-results/window-functions)

## Combining with Other Window Functions

SUM() OVER works alongside ROW_NUMBER, RANK, LAG, and [COUNT() OVER](https://www.dawiso.com/glossary/sql-count-complete-guide-to-counting-records-in-database-queries) 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](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning) 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.

[See it in action](https://www.dawiso.com/product/data-catalog)

Data & Analytics Catalog

Create a unified view of your data assets and gain insights faster with automated data discovery.

### Table of contents

SUM vs SUM() OVERRunning TotalsPartitioned Running TotalsFrame SpecificationsPractical PatternsCombining Window FunctionsPerformance

## Related Terms

### [SQL PARTITION BY - Complete Guide to Window Functions and Data Partitioning](https://www.dawiso.com/glossary/sql-partition-by-complete-guide-to-window-functions-and-data-partitioning)

Learn SQL PARTITION BY for advanced window functions and data partitioning techniques

Read more →

### [SQL SUM-AVG-MIN-MAX - Complete Guide to SQL Aggregate Functions](https://www.dawiso.com/glossary/sql-sum-avg-min-max-complete-guide-to-sql-aggregate-functions)

Master SQL aggregate functions SUM, AVG, MIN, MAX for data analysis and calculations

Read more →

### [What Is Text-to-SQL?](https://www.dawiso.com/glossary/text-to-sql)

Text-to-SQL turns plain-language questions into SQL using an LLM. See how it works, why accuracy depends on context, and where it breaks in the enterprise.

Read more →

### [SQL COUNT - Complete Guide to Counting Records in Database Queries](https://www.dawiso.com/glossary/sql-count-complete-guide-to-counting-records-in-database-queries)

Master SQL COUNT function for accurate database record counting and data analysis

Read more →

### [SQL DELETE - Complete Guide to Removing Database Records](https://www.dawiso.com/glossary/sql-delete-complete-guide-to-removing-database-records)

Learn SQL DELETE statement for safe database record removal and data management

Read more →

### [SQL GROUP BY - Complete Guide to Data Aggregation and Analysis](https://www.dawiso.com/glossary/sql-group-by-complete-guide-to-data-aggregation-and-analysis)

Master SQL GROUP BY for powerful data aggregation, grouping, and business analytics

Read more →
