SQL
Calculating Running Totals or Cumulative Sums in SQL
Compute running totals or cumulative sums over a specified order within your dataset using SQL window functions, useful for financial reports and analytics.
SELECT
order_id,
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total_amount
FROM orders
ORDER BY order_date, order_id;
-- To calculate running total within groups (e.g., per customer):
-- SELECT
-- customer_id,
-- order_id,
-- order_date,
-- amount,
-- SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS running_total_per_customer
-- FROM orders
-- ORDER BY customer_id, order_date, order_id;
-- Example 'orders' table structure:
-- CREATE TABLE orders (
-- order_id INT PRIMARY KEY AUTO_INCREMENT,
-- customer_id INT,
-- order_date DATE,
-- amount DECIMAL(10, 2)
-- );
-- INSERT INTO orders (customer_id, order_date, amount) VALUES
-- (1, '2023-01-05', 100.00),
-- (2, '2023-01-10', 250.50),
-- (1, '2023-01-15', 120.00),
-- (3, '2023-01-20', 300.00),
-- (2, '2023-01-25', 180.00),
-- (1, '2023-01-30', 90.00);
How it works: This query uses the `SUM()` window function to calculate a running total (cumulative sum) of `amount` based on the `order_date` and `order_id`. The `OVER (ORDER BY ...)` clause defines the window and the order of summation. An optional `PARTITION BY` clause can be added to restart the running total for each group (e.g., per customer), making it highly versatile for analytical and financial reporting.