Running totals with SUM OVER in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUM() OVER() function do in SQL?

It calculates a running total or cumulative sum of values over a specified order without collapsing rows.

beginner

How do you specify the order for a running total using SUM() OVER()?

You use the ORDER BY clause inside the OVER() clause to define the order of rows for the running total.

intermediate

What is the difference between SUM() OVER() and a regular SUM() with GROUP BY?

SUM() OVER() returns a running total for each row, keeping all rows visible. Regular SUM() with GROUP BY aggregates rows into groups, returning one row per group.

beginner

Write a simple SQL query to calculate a running total of sales ordered by date.

SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_table;

intermediate

Can SUM() OVER() be used without an ORDER BY clause? What happens?

Yes, but without ORDER BY, it sums over the entire partition and returns the same total for every row, not a running total.

What does the ORDER BY clause inside SUM() OVER() control?

A The filtering of rows before summing

B The order of the final query result only

C The order in which rows are summed for the running total

D The grouping of rows for aggregation

The ORDER BY inside OVER() defines the sequence in which rows are processed to calculate the running total.

Which SQL clause is necessary to calculate a running total with SUM() OVER()?

A HAVING

B GROUP BY

C WHERE

D ORDER BY inside OVER()

The running total requires ORDER BY inside the OVER() clause to define the order of accumulation.

What will SUM(sales) OVER () without ORDER BY return?

A The total sales repeated on every row

B A running total by date

C An error

D Only the first row's sales

Without ORDER BY, the sum is over the entire partition, so every row shows the same total.

Which of these is a correct use of SUM() OVER() to get a running total?

A SELECT SUM(sales) FROM sales_table;

B SELECT sales, SUM(sales) OVER (ORDER BY date) FROM sales_table;

C SELECT sales, SUM(sales) FROM sales_table GROUP BY date;

D SELECT sales, SUM(sales) OVER () FROM sales_table ORDER BY date;

Option B correctly uses SUM() OVER (ORDER BY date) to calculate a running total.

What is the main benefit of using SUM() OVER() for running totals?

A It calculates totals without losing individual row details

B It filters rows based on sum

C It reduces the number of rows returned

D It sorts the table permanently

Using SUM() OVER() keeps all rows and adds a running total column, preserving detail.

Explain how to calculate a running total of sales by date using SQL window functions.

Think about how to add a cumulative sum column without grouping rows.

Describe the difference between using SUM() with GROUP BY and SUM() OVER() for totals.

Consider how the number of rows changes and what totals represent.