A running total can look suspicious when several records suddenly receive the same cumulative value. The arithmetic may be correct. The query may simply be grouping equal ordering values into the same analytic window.
Oracle’s ROWS and RANGE window frames answer different questions. Understanding that difference helps you decide whether a report should advance transaction by transaction or treat records with the same ordering value together.
Start with a small example
Imagine three transactions for one account. Two share the same timestamp and have amounts of 10 and 20. A later transaction adds 5.
A transaction-by-transaction running total might be 10, 30, and 35. A running total through each timestamp would show 30 for both transactions at the first timestamp, followed by 35.
These results describe different reporting intentions. Before writing the query, ask whether the reader needs the balance after each individual transaction or the accumulated amount through each recorded time.
Oracle supports SUM as an analytic function, allowing the cumulative result to appear beside the underlying rows instead of collapsing them into a single aggregate row.
Use ROWS for an explicit sequence
A physical ROWS frame counts positions in the ordered row sequence. For a cumulative transaction report, a common form is:
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY occurred_at, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)Here, the intended sequence is defined by timestamp and then transaction identifier. The example assumes that this combination uniquely orders transactions within an account.
Oracle documents that physical windows can produce nondeterministic results when the ordering does not establish a unique sequence. The final query also needs its own ORDER BY when the display order matters.
For the example above, transaction identifiers provide a consistent order between the two events sharing a timestamp. Whether that order matches the business meaning still needs to be decided.
Use RANGE when equal ordering values belong together
For a running total through a timestamp, the frame can be written as:
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY occurred_at
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)With this frame, rows sharing the current ordering value belong to the same cumulative boundary. In the sample dataset, both transactions at the first timestamp therefore include the amounts 10 and 20.
For an ordered analytic SUM, omitting the window clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Writing the frame explicitly makes that choice easier to see during review.
Review the meaning before the presentation
A useful test dataset should contain ties. A dataset where every timestamp is different can make two differently intended queries appear equivalent.
Also test more than one account so that an omitted or incorrect partition becomes visible. Compare a few expected values by hand before applying currency formatting or building a chart.
Finally, name the output according to its meaning. “Balance after transaction” and “Total through timestamp” help readers understand why repeated cumulative values may be intentional. A running total becomes trustworthy when its sequence and boundary match the question the report is supposed to answer.