Running Total in SQL

Level 5199 keystrokes

A window SUM with the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a running total.

SQL code

-- Return each completed transaction with a running total
1SELECT
2 order_date,
3 amount,
-- SUM used as a window function keeps every row
4 SUM(amount) OVER (
-- Rows are added up in date order
5 ORDER BY order_date
-- Frame: from the first row of the window...
6 ROWS BETWEEN UNBOUNDED PRECEDING
-- ...up to the current row, counting rows one by one even if dates are equal
7 AND CURRENT ROW
8 ) AS running_total
9FROM
10 transactions
-- Only completed transactions are summed
11WHERE
12 status = 'completed'
-- Show the rows in date order
13ORDER BY
14 order_date;

Output

+------------+--------+---------------+
| order_date | amount | running_total |
+------------+--------+---------------+
| 2024-01-01 |    100 |           100 |
| 2024-01-02 |    250 |           350 |
| 2024-01-03 |    175 |           525 |
| 2024-01-04 |    300 |           825 |
+------------+--------+---------------+
4 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 4
  • ,× 2
  • '× 2
  • (× 2
  • )× 2
  • ;× 1
  • =× 1

More SQL examples at level 5

← SQL typing practice