Running Totals with Window Functions

What are Running Totals?

A running total (cumulative sum) adds each row's value to the sum of all previous rows. Window functions make this straightforward with SUM() OVER(ORDER BY ...).

Syntax

SELECT column,
  SUM(column) OVER (ORDER BY order_column) AS running_total
FROM table_name;

When to Use

• Tracking cumulative revenue over time
• Showing account balance progression
• Monitoring cumulative enrollment counts

Key Points

1. Default Frame — SUM() OVER (ORDER BY col) defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
2. PARTITION BY — Add PARTITION BY to restart the running total per group.
3. Moving Average — Use AVG() OVER (ORDER BY col ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) for a 3-row moving average.
4. Running Count — COUNT(*) OVER (ORDER BY col) gives a running count.
5. Performance — Window functions are efficient; the database scans once and accumulates.