Aggregate Window Functions (SUM, AVG, COUNT OVER) in SQL

SUM, AVG, and COUNT used with OVER give you two different behaviors depending on whether you include ORDER BY inside OVER.

Without ORDER BY inside OVER, the aggregate computes a single value for the partition and places that same value on every row. With ORDER BY inside OVER, the aggregate accumulates: each row gets the sum (or average, or count) of all rows up to and including its position in the ordering. These are two different analytical questions, and the presence or absence of ORDER BY is what separates them.

You're tracking order revenue over time. You want each order to show both the overall total and the running total as of that date:

grand_total is the same on every row — the sum of all orders. running_total starts small and grows with each row. On the last row, it equals grand_total.

The same logic applies to other aggregates. Add PARTITION BY to reset within groups:

Each accumulates within its partition, restarting at the start of each new group.

COUNT(*) counts rows regardless of NULL. COUNT(column) counts only non-NULL values. SUM, AVG, MIN, and MAX all skip NULL values, consistent with how they behave as regular aggregates.

The one thing that trips people up: when multiple rows share the same ORDER BY value, the running total jumps ahead.

PostgreSQL's default behavior for ordered windows groups rows with the same sort value together and includes all of them in each other's running total. If three orders all have the same created_at timestamp, each of those three rows shows a running total that already includes all three of their amounts — not a strict row-by-row accumulation.

This is called RANGE mode. If you want strict row-by-row accumulation regardless of equal sort values, add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

In ROWS mode, each row accumulates only up to its own physical position. For time-series work where multiple events share a timestamp, this usually produces the result you expect.