Tracking how a metric grows over time is a common analytics requirement. You might need to see how revenue accumulates day by day, how many tickets have been resolved so far this month, or how inventory levels change after every transaction. These are all examples of running totals (also called cumulative sums). Instead of writing complex self-joins or slow correlated subqueries, modern SQL window functions let you compute running totals cleanly and efficiently.
If you’re working with reporting dashboards or operational datasets, learning this pattern is a core skill often covered in data analytics coaching in Bangalore, because it shows up across domains like finance, sales, HR analytics, and product analytics.
What Is a Running Total?
A running total is the cumulative sum of a measure over an ordered sequence. Conceptually:
- You choose an order (e.g., by date, by transaction time, by invoice number).
- For each row, you sum the current value plus all previous values in that order.
For example, if daily sales are 100, 150, 120, the running totals become 100, 250, 370.
Running totals help you answer questions like:
- “Are we on track to hit the monthly target?”
- “How quickly is revenue accumulating compared to last month?”
- “When did cumulative sign-ups cross a threshold?”
The Core SQL Pattern: SUM() OVER (ORDER BY …)
The most common running total is a cumulative sum using SUM() as a window function:
SELECT
order_date,
sales_amount,
SUM(sales_amount) OVER (ORDER BY order_date) AS running_sales
FROM daily_sales
ORDER BY order_date;
What’s happening here?
- SUM(sales_amount) is computed over a window of rows.
- OVER (ORDER BY order_date) tells SQL to consider rows in date order.
- For each row, SQL sums values from the start up to the current row.
This is far more readable than older approaches and usually performs better because databases can optimise window calculations well. Mastering this pattern is a practical milestone for many learners in data analytics coaching in Bangalore, especially those preparing for analytics roles that involve SQL-heavy work.
Grouped Running Totals with PARTITION BY
Often, you need separate running totals for each group,like per customer, per product, or per store. That’s where PARTITION BY comes in:
SELECT
store_id,
order_date,
sales_amount,
SUM(sales_amount) OVER (
PARTITION BY store_id
ORDER BY order_date
) AS running_store_sales
FROM daily_sales
ORDER BY store_id, order_date;
Here:
- PARTITION BY store_id resets the running total for each store.
- ORDER BY order_date still controls the accumulation sequence within each store.
This is essential for segmented reporting and cohort-style tracking.
Window Frames: ROWS vs RANGE and Why It Matters
Many databases use a default window frame when you specify ORDER BY. However, subtle differences can appear when the ordering column has ties (e.g., multiple transactions at the same timestamp or same date).
To make behaviour explicit, you can define a frame:
SUM(sales_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_sales
Key idea:
- ROWS accumulates row-by-row, including exactly the previous rows plus the current row.
- RANGE can treat rows with the same ORDER BY value as a group, which may cause the same running total to appear across tied rows.
When your dataset can have duplicate ordering values, using an explicit ROWS frame is safer and more predictable.
Practical Tips: Nulls, Filtering, and Performance
A few real-world considerations make running totals more reliable:
1) Handle NULL measures
If sales_amount can be NULL, most databases ignore NULLs in SUM(), but it’s still good practice to standardise inputs:
SUM(COALESCE(sales_amount, 0)) OVER (ORDER BY order_date) AS running_sales
2) Filter at the right stage
If you filter rows before calculating, you change what counts as “previous.” For example, filtering out refunds first might make your running total look artificially smooth. Decide whether to include all events, then optionally filter after computing.
3) Add deterministic ordering
If multiple rows share the same date, include a secondary sort (like transaction_id) to guarantee consistent results:
OVER (ORDER BY order_date, transaction_id)
4) Think about indexes and data volume
On large tables, performance improves when the database can scan in the same order you compute. Indexes on (partition_key, order_key) can help. These optimisation habits are frequently emphasised in data analytics coaching in Bangalore, because they translate directly to faster dashboards and more stable pipelines.
Conclusion
Running total calculations are a foundational analytics technique, and window functions are the cleanest way to implement them in SQL. With SUM() OVER (ORDER BY …), you can compute cumulative metrics in a readable, scalable manner. Add PARTITION BY to reset totals per group, use explicit window frames to avoid surprises with ties, and apply practical safeguards for NULLs and deterministic ordering.
Once you’re comfortable with these patterns, you’ll find they unlock a wide range of time-based and progression-based analyses,exactly the kind of SQL skill that employers expect and that learners refine through data analytics coaching in Bangalore.
