SQL - SUM() as Window Function (Running Total)
• Window functions with SUM() maintain access to individual rows while performing aggregations, unlike GROUP BY which collapses rows into summary results
Key Insights
• Window functions with SUM() maintain access to individual rows while performing aggregations, unlike GROUP BY which collapses rows into summary results • Running totals require the ROWS BETWEEN clause to define the frame of rows included in each calculation, with UNBOUNDED PRECEDING being the most common pattern • Partitioning window functions allows independent running totals per group, essential for multi-dimensional analysis like per-customer or per-product calculations
Understanding SUM() as a Window Function
The SUM() window function calculates aggregates across a defined “window” of rows while preserving individual row details. Unlike standard GROUP BY aggregations that collapse data, window functions add calculated columns alongside existing data.
Basic syntax:
SUM(column_name) OVER (
[PARTITION BY partition_column]
[ORDER BY order_column]
[ROWS BETWEEN frame_start AND frame_end]
)
The OVER clause transforms SUM() from a simple aggregate into a window function. Without OVER, SUM() requires GROUP BY and collapses rows. With OVER, each row retains its identity.
Simple Running Total
A running total accumulates values from the first row to the current row. This requires ordering data and defining the window frame:
CREATE TABLE sales (
sale_date DATE,
amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
('2024-01-01', 100.00),
('2024-01-02', 150.00),
('2024-01-03', 200.00),
('2024-01-04', 120.00),
('2024-01-05', 180.00);
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales
ORDER BY sale_date;
Results:
sale_date | amount | running_total
------------|--------|---------------
2024-01-01 | 100.00 | 100.00
2024-01-02 | 150.00 | 250.00
2024-01-03 | 200.00 | 450.00
2024-01-04 | 120.00 | 570.00
2024-01-05 | 180.00 | 750.00
The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause defines the frame: from the first row to the current row. This is so common that most databases allow shorthand:
SUM(amount) OVER (ORDER BY sale_date) AS running_total
Partitioned Running Totals
PARTITION BY creates independent running totals for each group, resetting the calculation at partition boundaries:
CREATE TABLE product_sales (
sale_date DATE,
product_id INT,
quantity INT
);
INSERT INTO product_sales VALUES
('2024-01-01', 1, 10),
('2024-01-01', 2, 5),
('2024-01-02', 1, 15),
('2024-01-02', 2, 8),
('2024-01-03', 1, 12),
('2024-01-03', 2, 6);
SELECT
sale_date,
product_id,
quantity,
SUM(quantity) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS product_running_total,
SUM(quantity) OVER (
ORDER BY sale_date, product_id
) AS overall_running_total
FROM product_sales
ORDER BY sale_date, product_id;
Results:
sale_date | product_id | quantity | product_running_total | overall_running_total
------------|------------|----------|----------------------|----------------------
2024-01-01 | 1 | 10 | 10 | 10
2024-01-01 | 2 | 5 | 5 | 15
2024-01-02 | 1 | 15 | 25 | 30
2024-01-02 | 2 | 8 | 13 | 38
2024-01-03 | 1 | 12 | 37 | 50
2024-01-03 | 2 | 6 | 19 | 56
The partitioned running total resets for each product_id, while the overall running total continues across all rows.
Moving Averages and Custom Windows
Window frames don’t need to start from the beginning. Define custom ranges for moving averages or rolling calculations:
SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS three_day_sum,
ROUND(AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS three_day_avg
FROM sales
ORDER BY sale_date;
Results:
sale_date | amount | three_day_sum | three_day_avg
------------|--------|---------------|---------------
2024-01-01 | 100.00 | 100.00 | 100.00
2024-01-02 | 150.00 | 250.00 | 125.00
2024-01-03 | 200.00 | 450.00 | 150.00
2024-01-04 | 120.00 | 470.00 | 156.67
2024-01-05 | 180.00 | 500.00 | 166.67
The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current row plus the two rows before it, creating a 3-row sliding window.
Percentage of Total Calculations
Combine window functions to calculate each row’s contribution to the total:
SELECT
sale_date,
amount,
SUM(amount) OVER () AS total_sales,
ROUND(100.0 * amount / SUM(amount) OVER (), 2) AS percent_of_total,
ROUND(100.0 * SUM(amount) OVER (ORDER BY sale_date) /
SUM(amount) OVER (), 2) AS cumulative_percent
FROM sales
ORDER BY sale_date;
Results:
sale_date | amount | total_sales | percent_of_total | cumulative_percent
------------|--------|-------------|------------------|-------------------
2024-01-01 | 100.00 | 750.00 | 13.33 | 13.33
2024-01-02 | 150.00 | 750.00 | 20.00 | 33.33
2024-01-03 | 200.00 | 750.00 | 26.67 | 60.00
2024-01-04 | 120.00 | 750.00 | 16.00 | 76.00
2024-01-05 | 180.00 | 750.00 | 24.00 | 100.00
An empty OVER() clause creates a window containing all rows, useful for calculating grand totals.
Practical Application: Financial Analysis
Combine multiple window functions for comprehensive financial reporting:
CREATE TABLE transactions (
transaction_date DATE,
account_id INT,
transaction_type VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO transactions VALUES
('2024-01-01', 1001, 'CREDIT', 1000.00),
('2024-01-02', 1001, 'DEBIT', -150.00),
('2024-01-03', 1001, 'CREDIT', 500.00),
('2024-01-04', 1001, 'DEBIT', -200.00),
('2024-01-01', 1002, 'CREDIT', 2000.00),
('2024-01-02', 1002, 'DEBIT', -300.00),
('2024-01-03', 1002, 'CREDIT', 750.00);
SELECT
transaction_date,
account_id,
transaction_type,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date
) AS account_balance,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) OVER (
PARTITION BY account_id
ORDER BY transaction_date
) AS total_credits,
SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END) OVER (
PARTITION BY account_id
ORDER BY transaction_date
) AS total_debits
FROM transactions
ORDER BY account_id, transaction_date;
Results:
transaction_date | account_id | transaction_type | amount | account_balance | total_credits | total_debits
-----------------|------------|------------------|----------|-----------------|---------------|-------------
2024-01-01 | 1001 | CREDIT | 1000.00 | 1000.00 | 1000.00 | 0.00
2024-01-02 | 1001 | DEBIT | -150.00 | 850.00 | 1000.00 | -150.00
2024-01-03 | 1001 | CREDIT | 500.00 | 1350.00 | 1500.00 | -150.00
2024-01-04 | 1001 | DEBIT | -200.00 | 1150.00 | 1500.00 | -350.00
2024-01-01 | 1002 | CREDIT | 2000.00 | 2000.00 | 2000.00 | 0.00
2024-01-02 | 1002 | DEBIT | -300.00 | 1700.00 | 2000.00 | -300.00
2024-01-03 | 1002 | CREDIT | 750.00 | 2450.00 | 2750.00 | -300.00
This approach provides running balances per account while tracking separate credit and debit totals.
Performance Considerations
Window functions process data in memory after filtering and joining. Optimize performance by:
Indexing ORDER BY columns: Create indexes on columns used in ORDER BY clauses within window functions.
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_product_sales_product_date ON product_sales(product_id, sale_date);
Filtering before windowing: Apply WHERE clauses before window function evaluation.
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2024-01-01' -- Filter first
ORDER BY sale_date;
Limiting partitions: Excessive partitions increase memory usage. Consider pre-aggregating data or using CTEs to reduce partition counts.
Window functions with SUM() provide powerful analytical capabilities without sacrificing row-level detail, making them essential for financial reporting, trend analysis, and data exploration.