Running totals with SUM() OVER

A running total adds up values row by row, giving you a cumulative sum that grows with each new entry rather than one grand total at the end.

About 5–10 minutes · no sign-up needed

Example

Tracking Retail Running Revenue with SUM() OVER

SELECT order_id, order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date

Result:

order_id	order_date	revenue	running_total
O2001	2024-03-01	120.5	120.5
O2002	2024-03-02	85.0	205.5
O2003	2024-03-03	200.75	406.25
O2004	2024-03-04	55.25	461.5
O2005	2024-03-05	310.0	771.5

Run the query without SUM so you can see what the unfiltered values look like.

SELECT order_id, order_date, revenue,
       revenue OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

SUM totals the numbers in a column. Works on numeric columns only.

SELECT order_id, order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

AS gives the SUM result a friendly column name so the output reads cleanly.

SELECT order_id, order_date, revenue,
       SUM(revenue) AS sum_result OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

Try each one in your head first, then open the answer.

1. Using orders, write a query that calculates a running total of revenue ordered by order_date.

SELECT order_date, revenue, SUM(revenue) OVER (ORDER BY order_date) FROM orders

2. Using store_sales, write a query that calculates a running total of revenue ordered by sale_date.

SELECT sale_date, revenue, SUM(revenue) OVER (ORDER BY sale_date) FROM store_sales

3. Using products, write a query that calculates a running total of price ordered by price.

SELECT price, price, SUM(price) OVER (ORDER BY price) FROM products

4. Using employees, write a query that calculates a running total of salary ordered by hire_date.

SELECT hire_date, salary, SUM(salary) OVER (ORDER BY hire_date) FROM employees

5. Using reviews, write a query that calculates a running total of rating ordered by review_date.

SELECT review_date, rating, SUM(rating) OVER (ORDER BY review_date) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed