Скачать или смотреть 📊 SQL One-Liner: Running Total (Cumulative Sum) Made Easy

The running total (or cumulative sum) is a cornerstone in financial reporting, inventory tracking, and analytics. Traditionally, it required correlated subqueries or self-joins to accumulate values row by row. While this works, it quickly becomes inefficient on large tables.

Enter the window function approach:

SUM(amount) OVER (ORDER BY order_date)


With this one-liner, SQL engines compute the running total in a single scan, delivering both performance and simplicity.

This technique is widely supported in PostgreSQL, MySQL (8+), SQL Server, Oracle, and BigQuery. If your SQL engine supports window functions, always prefer this shortcut for cleaner, faster queries.

✅ Long Way (Self-Join or Correlated Subquery):

SELECT
o.order_id,
o.order_date,
o.amount,
(
SELECT SUM(o2.amount)
FROM orders o2
WHERE o2.order_date v= o.order_date
) AS running_total
FROM orders o
ORDER BY o.order_date;


Explanation:

For each row in orders, a correlated subquery computes the sum of all amounts up to that row’s date.

This works but is inefficient since it executes the subquery for every row.

✅ Shortcut One-Liner (Window Function):

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


Explanation:

The window function SUM() OVER (ORDER BY order_date) efficiently computes the cumulative sum in one pass.

It avoids repeated scans and scales much better for large datasets.

Perfect for financial reports, trend analysis, or real-time dashboards.