Running total without window functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a running total in SQL?

A running total is a cumulative sum of values in a column, calculated row by row, adding each value to the sum of all previous values.

intermediate

How can you calculate a running total without using window functions?

You can calculate a running total by using a correlated subquery that sums all rows up to the current row based on an ordering column.

intermediate

Write a simple SQL query snippet to calculate a running total without window functions.

SELECT t1.id, t1.amount, (SELECT SUM(t2.amount) FROM table_name t2 WHERE t2.id <= t1.id) AS running_total FROM table_name t1 ORDER BY t1.id;

intermediate

Why might using window functions be preferred over correlated subqueries for running totals?

Window functions are usually more efficient and easier to read because they avoid repeated subqueries and can process rows in a single pass.

beginner

What is a correlated subquery in the context of running totals?

A correlated subquery is a subquery that refers to the outer query's current row, allowing it to calculate sums up to that row for the running total.

Which SQL clause is essential to order rows when calculating a running total without window functions?

A GROUP BY

B ORDER BY

C HAVING

D WHERE

ORDER BY is used to define the sequence of rows so the running total sums values in the correct order.

What does a correlated subquery do in a running total calculation?

A Calculates the sum of all rows up to the current row

B Filters rows based on a condition

C Joins two tables

D Deletes duplicate rows

The correlated subquery sums values from the start up to the current row to produce the running total.

Which of these is a disadvantage of using correlated subqueries for running totals?

A They do not require ordering

B They are always faster than window functions

C They can be slow on large datasets

D They cannot calculate sums

Correlated subqueries run a subquery for each row, which can be slow on large tables.

If you cannot use window functions, what SQL feature helps to calculate running totals?

A Stored procedures

B Triggers

C Indexes

D Correlated subqueries

Correlated subqueries allow calculating running totals by summing rows up to the current one.

In the query: SELECT t1.id, (SELECT SUM(t2.amount) FROM table t2 WHERE t2.id <= t1.id) FROM table t1, what does 't2.id <= t1.id' ensure?

A It sums amounts from the first row up to the current row

B It filters out all rows

C It sums only the current row

D It orders the rows randomly

The condition sums all amounts from rows with id less than or equal to the current row's id, creating a running total.

Explain how to calculate a running total in SQL without using window functions.

Think about summing all previous rows for each row.

Describe the advantages and disadvantages of using correlated subqueries for running totals compared to window functions.

Consider performance and compatibility.