When working with sales data, you may come across a requirement to calculate how much revenue a business has generated up to a particular date.
For example, a sales manager may want to see daily sales along with the total sales accumulated from the beginning of the month.
This is called a Running Total or Cumulative Sum.
In SQL, we can calculate it easily using the "SUM()" window function with the "OVER()" clause.
In this SQL Challenge #105, let's see how to calculate a running total using Oracle SQL.
The Sales Table
Assume we have a table named "sales" containing daily sales information.
sale_date| daily_sales
01-Nov-2026| 1000
02-Nov-2026| 1500
03-Nov-2026| 800
04-Nov-2026| 2000
05-Nov-2026| 1200
Our requirement is to calculate the cumulative sales for each date.
Your Challenge
Write an SQL query to:
- Display sale date, daily sales and running total.
- Use "SUM()" to calculate cumulative sales.
- Arrange records by sale date, from oldest to newest.
- Add each day's sales to the previous running total.
- Display the running total for every date.
- Get the result without changing the original sales table.
Can you solve it before looking at the solution?
The SQL Solution (Oracle)
SELECT
sale_date,
daily_sales,
SUM(daily_sales) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM sales
ORDER BY sale_date;
Expected Output
sale_date| daily_sales| running_total
01-Nov-2026| 1000| 1000
02-Nov-2026| 1500| 2500
03-Nov-2026| 800| 3300
04-Nov-2026| 2000| 5300
05-Nov-2026| 1200| 6500
Notice how the running total increases with each new day's sales.
For example:
- Day 1: 1000
- Day 2: 1000 + 1500 = 2500
- Day 3: 2500 + 800 = 3300
- Day 4: 3300 + 2000 = 5300
- Day 5: 5300 + 1200 = 6500
Why Does This SQL Query Work?
Let's understand the important parts of the query.
- SUM(daily_sales)
Adds the daily sales amounts.
- OVER()
Allows us to use SUM() as a window function without combining all records into one row.
- ORDER BY sale_date
Processes the sales records in chronological order.
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This defines the window frame:
- "UNBOUNDED PRECEDING" starts from the first row.
- "CURRENT ROW" includes the current row in the calculation.
As a result, every row displays the total sales from the first date up to that particular date.
One Thing to Remember
There is an important difference between a normal aggregate function and a window function.
- "SUM()": Calculates the total for a group of records.
- "SUM() OVER()": Calculates totals while keeping individual rows.
- "ORDER BY": Determines the sequence of the running calculation.
- "PARTITION BY": Allows separate running totals for different groups.
Window functions are especially useful when building sales reports, financial reports and business dashboards.
Your Turn
How would you modify this query to calculate a separate running total for each product using "PARTITION BY"?
Can you solve it without using a subquery or self-join?
Share your SQL query in the comments.
Follow this SQL Challenge series for more practical SQL problems covering joins, subqueries, CTEs, window functions, performance tuning and real-world reporting.