SQL
Calculate Running Totals (Cumulative Sum) without Window Functions
Discover how to compute a running total or cumulative sum in SQL using a self-join and aggregation, offering an alternative to window functions.
SELECT
o1.order_date,
o1.amount,
SUM(o2.amount) AS running_total
FROM
orders o1
JOIN
orders o2 ON o2.order_date <= o1.order_date
GROUP BY
o1.order_date, o1.amount -- Group by all non-aggregated columns from o1
ORDER BY
o1.order_date;
-- This assumes unique (order_date, amount) pairs.
-- If there are multiple orders on the same date, you might need an additional
-- column (like order_id) in the JOIN condition and GROUP BY to ensure correct ordering.
-- Example with order_id for finer granularity:
-- SELECT
-- o1.order_id,
-- o1.order_date,
-- o1.amount,
-- SUM(o2.amount) AS running_total
-- FROM
-- orders o1
-- JOIN
-- orders o2 ON o2.order_date < o1.order_date OR (o2.order_date = o1.order_date AND o2.order_id <= o1.order_id)
-- GROUP BY
-- o1.order_id, o1.order_date, o1.amount
-- ORDER BY
-- o1.order_date, o1.order_id;
How it works: This SQL snippet demonstrates how to calculate a running total, also known as a cumulative sum, without relying on window functions. It achieves this by using a self-join. The `orders` table is joined with itself (`o1` and `o2`) such that for each row in `o1`, `o2` includes all rows from `orders` that occurred on or before `o1.order_date`. The `SUM(o2.amount)` then aggregates the amounts for all these earlier or same-date orders, providing the running total. The `GROUP BY` clause is essential to aggregate the sum correctly for each distinct `o1` row.