Running totals, and the frame you didn't ask for

Add ORDER BY inside OVER and an aggregate becomes a running one: sum(runs) over (order by over_no) is the score so far. Cricket scorers call the chart of it "the worm". This page builds the worm, and then shows the default that quietly changes your answer.

The test case

For both innings of the 2026 final (match 32), show the score and wickets at the end of overs 5, 10, 15 and 20, as cricket scoreboards do.

Expected result

Chennai were 63/1 after five overs and finished 160 all out; Leeds were cruising at 153/4 after 15 and got home in the 17th over, which is why there's no "over 20" for the chase.

Way 1: per over, then a running SUM

with per_over as (
  select innings_no, over_no, sum(total_runs) as runs, sum(is_wicket) as wickets
  from   cr_balls_v
  where  match_id = 32
  group  by innings_no, over_no
),
running as (
  select innings_no, over_no, runs,
         sum(runs)    over (partition by innings_no order by over_no) as score,
         sum(wickets) over (partition by innings_no order by over_no) as wkts
  from   per_over
)
select innings_no, over_no, runs, score, wkts
from   running
where  over_no in (5, 10, 15, 20)
order  by innings_no, over_no;

The filter is in the outer query. Put where over_no in (5, 10, 15, 20) in the same query as the window functions and the running sum only sees those four overs: "score after 10 overs" would be 24 instead of 96. WHERE runs before window functions (page 5.0).

Way 2: a correlated subquery

with per_over as (
  select innings_no, over_no, sum(total_runs) as runs, sum(is_wicket) as wickets
  from   cr_balls_v
  where  match_id = 32
  group  by innings_no, over_no
)
select o.innings_no, o.over_no, o.runs,
       (select sum(x.runs)    from per_over x where x.innings_no = o.innings_no and x.over_no <= o.over_no) as score,
       (select sum(x.wickets) from per_over x where x.innings_no = o.innings_no and x.over_no <= o.over_no) as wkts
from   per_over o
where  o.over_no in (5, 10, 15, 20)
order  by o.innings_no, o.over_no;

"Everything up to and including this over", literally. Here the filter can sit in the main query, because each subquery reads per_over itself. Correct, readable, and slow on big data: every row re-adds all the rows before it.

The default frame: RANGE, not ROWS

When you write ORDER BY in OVER and no frame, Oracle uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE means "all rows whose ORDER BY value is up to this row's", and that includes rows that tie with the current one.

Watch what that does on ball-by-ball rows ordered by over:

The test case

For the first two overs of the final, show each ball's runs, the running total ordered by over_no with the default frame, and the running total ordered by seq_no.

select over_ball, total_runs,
       sum(total_runs) over (order by over_no) as by_over_default,
       sum(total_runs) over (order by seq_no)  as by_seq
from   cr_balls_v
where  match_id = 32 and innings_no = 1 and over_no <= 2
order  by seq_no;

Every ball of over 1 shows 19, the total at the end of the over, because all six balls tie on over_no = 1 and RANGE includes all of them. Ordered by seq_no, which is unique, there are no ties and the total really runs.

The fix is one of:

• Order by something unique (seq_no), so ties can't happen. Usually the right answer.
• Say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That counts physical rows, so ties don't matter, but then the order within a tie is random unless the ORDER BY is unique. Which brings you back to the first fix.

19c and 26ai

The same on both. 26ai adds a third frame type, GROUPS (page 12.4).

Gotchas

• The default frame is RANGE. Running totals over non-unique orders give "end of group" totals.
• Filter after windowing. In the same query, WHERE changes what the window sees.
• Running COUNT(*) is a row number only when the order is unique, for the same reason.