Window functions: totals without losing the rows

GROUP BY answers "how many runs did the team score?" by squashing many rows into one. Often you want the total and the rows: each batter's runs next to the team total, each over's runs next to the score so far. That's what window functions do. They compute across a set of rows (the "window") and write the answer on every row, without squashing anything.

Any aggregate becomes a window function when you add OVER (...):

Inside OVER: PARTITION BY splits the rows into groups (like GROUP BY, minus the squashing), ORDER BY puts each group in order, and an optional frame (ROWS, RANGE, page 5.2) says which rows around the current one count.

Window functions run late, after WHERE, GROUP BY and HAVING:

That order is why you can't put a window function in WHERE (it hasn't been computed yet), and why a window function can sit on top of a GROUP BY: sum(sum(runs)) over () is legal, and means "the total of the group totals".

The test case

In the first innings of the 2026 final (match 32), show each Chennai batter's runs, the team's runs off the bat, and each batter's share as a percentage (one decimal), in batting order.

Expected result

Vignesh Babu top-scored with 36 at number 3; three batters made ducks. In a final. Ouch.

Way 1: SUM() OVER ()

with by_batter as (
  select batter_id, min(seq_no) as first_ball, sum(runs_batter) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 1
  group  by batter_id
)
select p.player_name,
       b.runs,
       sum(b.runs) over ()                           as team_runs,
       round(100 * b.runs / sum(b.runs) over (), 1)  as pct
from   by_batter b
join   cr_players p on p.player_id = b.batter_id
order  by b.first_ball;

OVER () with nothing inside is a window of all the rows, so sum(b.runs) over () is the grand total, repeated on each row.

Way 2: RATIO_TO_REPORT

with by_batter as (
  select batter_id, min(seq_no) as first_ball, sum(runs_batter) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 1
  group  by batter_id
)
select p.player_name,
       b.runs,
       sum(b.runs) over ()                                as team_runs,
       round(100 * ratio_to_report(b.runs) over (), 1)    as pct
from   by_batter b
join   cr_players p on p.player_id = b.batter_id
order  by b.first_ball;

RATIO_TO_REPORT(x) OVER (...) is x / sum(x) over (...) in one function. An Oracle extra that saves typing whenever you need shares.

Way 3: join to a total (no window function)

with by_batter as (
  select batter_id, min(seq_no) as first_ball, sum(runs_batter) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 1
  group  by batter_id
),
total as (
  select sum(runs) as team_runs from by_batter
)
select p.player_name, b.runs, t.team_runs,
       round(100 * b.runs / t.team_runs, 1) as pct
from   by_batter b
cross  join total t
join   cr_players p on p.player_id = b.batter_id
order  by b.first_ball;

How it was done before window functions: compute the total separately and join it back. Fine for one total; with totals per team, per season and per phase you'd need a join for each, while window functions just add a column each.

19c and 26ai

The same on both. Window functions have been in Oracle since 8i (1999), long before most databases had them.

Gotchas

• WHERE runs first. Filter batters with WHERE and the total shrinks too. To keep the full total but show only some rows, filter in an outer query (or with QUALIFY on 26ai, page 12.2).
• Integer division isn't a thing in Oracle, so 100 * 29 / 152 is 19.07..., not 0. (In some other databases it would be.)
• OVER () means everything, which is easy to forget when you meant OVER (PARTITION BY team).