Running totals in SQL, and the ROWS vs RANGE trap

A running total is SUM with an ORDER BY inside the window:

SELECT order_date, id, status,
       SUM(1) OVER (ORDER BY order_date) AS running_orders
FROM orders
ORDER BY order_date
LIMIT 6;

Adding ORDER BY to an aggregate window quietly changes what it does: it now sums from the first row up to the current one.

Where it goes wrong

The default frame when you write ORDER BY without one is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal sort values as a single unit, so every row on the same date gets the same total, including the other orders from that day.

To see the difference, find a date with more than one order:

WITH busy AS (
  SELECT order_date FROM orders GROUP BY order_date HAVING COUNT(*) > 1 ORDER BY order_date LIMIT 1
)
SELECT o.order_date, o.id,
       SUM(1) OVER (ORDER BY o.order_date) AS using_range,
       SUM(1) OVER (ORDER BY o.order_date, o.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS using_rows
FROM orders o
WHERE o.order_date <= (SELECT order_date FROM busy)
ORDER BY o.order_date, o.id
LIMIT 12;

On the shared date, using_range stays flat across those rows while using_rows climbs by one each row.

Which to use

If you want a line per order climbing one at a time, use ROWS and give ORDER BY a tie-breaker. Without the tie-breaker, the order inside a shared date is arbitrary.

If you're charting one point per day, RANGE is giving you the right end-of-day figure, but it's cleaner to aggregate to one row per day first and then run the total:

WITH daily AS (
  SELECT order_date, COUNT(*) AS orders FROM orders GROUP BY order_date
)
SELECT order_date, orders,
       SUM(orders) OVER (ORDER BY order_date) AS running_orders
FROM daily
ORDER BY order_date
LIMIT 6;

With one row per date, there are no ties and the frame question goes away.

Resetting each month

PARTITION BY restarts the total. Here the first seven characters of an ISO date give the month:

WITH daily AS (
  SELECT order_date, COUNT(*) AS orders FROM orders GROUP BY order_date
)
SELECT order_date, orders,
       SUM(orders) OVER (PARTITION BY substr(order_date, 1, 7) ORDER BY order_date) AS month_to_date
FROM daily
ORDER BY order_date
LIMIT 6;