Aggregate Window Functions

The five aggregate functions you already know become far more powerful with OVER: each innings can be compared with its player's total, the season average, or the best score — all in one query, without losing a single row.

Definition

An aggregate window function is a normal aggregate (COUNT, SUM, AVG, MIN, MAX) used with an OVER() clause. It computes the aggregate over the row's window and returns the result on every row, so detail values and summary values can be used together in the same row.

• The NULL rules are exactly the same as for normal aggregates: COUNT(*) counts all rows, everything else ignores NULL.
• PARTITION BY, ORDER BY and frames are all optional for these functions.

Syntax

COUNT(* | expression)  OVER ([PARTITION BY ...] [ORDER BY ... [frame]])
SUM(expression)        OVER ([PARTITION BY ...] [ORDER BY ... [frame]])
AVG(expression)        OVER ([PARTITION BY ...] [ORDER BY ... [frame]])
MIN(expression)        OVER ([PARTITION BY ...] [ORDER BY ... [frame]])
MAX(expression)        OVER ([PARTITION BY ...] [ORDER BY ... [frame]])

Example — COUNT(*) vs COUNT(runs) in a window

SELECT id, player, runs,
    COUNT(*)    OVER (PARTITION BY player) AS all_rows,
    COUNT(runs) OVER (PARTITION BY player) AS with_runs
FROM innings;

Every player played 3 innings (COUNT(*)), but Dhoni has only 2 innings with a value (COUNT(runs) skips NULL). Use case: data-quality checks — "which players have missing scores?".

Example — SUM: part-to-whole percentage

SELECT id, player, runs,
    ROUND(runs * 100.0 / SUM(runs) OVER (), 1) AS pct_of_total
FROM innings;

Step by step: (1) SUM(runs) OVER () gives the grand total 645 on each row; (2) each row's share = runs × 100 ÷ 645. Buttler's 107 was 16.6% of all runs. 100.0 (not 100) forces decimal division — 82 * 100 / 645 would give 12 in SQL Server. NULL × anything = NULL. (SQL Server shows trailing zeros such as 12.700000; the value is the same.)

Example — AVG per player, and the NULL decision

SELECT id, player, runs,
    ROUND(AVG(runs * 1.0) OVER (PARTITION BY player), 1)              AS avg_default,
    ROUND(AVG(COALESCE(runs, 0) * 1.0) OVER (PARTITION BY player), 1) AS avg_with_zero
FROM innings
WHERE player IN ('Virat Kohli', 'MS Dhoni');

• Default AVG excludes NULL: Dhoni = (26 + 45) ÷ 2 = 35.5.
• COALESCE(runs, 0) counts NULL as 0: Dhoni = (26 + 0 + 45) ÷ 3 = 23.7.

Neither is "wrong" — it is a business decision. "Did not bat" should not lower a batting average, so 35.5 is correct for cricket. But for "average runs per match played", 23.7 is right. Always decide consciously how NULL should be treated. (All four averages: Kohli 71.3, Rohit 40.3, Dhoni 35.5, Buttler 79.7.)

Example — MIN & MAX: extremes per player

SELECT id, player, runs,
    MIN(runs) OVER (PARTITION BY player) AS lowest,
    MAX(runs) OVER (PARTITION BY player) AS highest
FROM innings;

Kohli: lowest 59, highest 82 · Rohit: 0 / 76 · Dhoni: 26 / 45 · Buttler: 59 / 107. MIN/MAX work on any data type — numbers, text and dates (e.g. MIN(match_date) OVER (PARTITION BY player) = each player's first match).

Example — compare each row with an aggregate

SELECT id, player, runs,
    MAX(runs) OVER ()        AS best,
    MAX(runs) OVER () - runs AS gap_to_best
FROM innings;

The same pattern works for "distance from the lowest" or "difference from the average".

Example — above or below average? (window + CASE)

SELECT id, player, runs,
    ROUND(AVG(runs * 1.0) OVER (), 1) AS avg_all,
    CASE WHEN runs > AVG(runs * 1.0) OVER () THEN 'Above' ELSE 'Below' END AS vs_avg
FROM innings;

The overall average is 645 ÷ 11 = 58.6. Window + CASE gives instant labels. Careful: NULL > 58.6 is not TRUE, so the NULL row falls into the ELSE → 'Below' branch. Add WHEN runs IS NULL THEN 'Did not bat' first if that matters.

Window functions on top of GROUP BY

You can combine both: GROUP BY first makes one row per team, then a window function works on those grouped rows.

SELECT team,
    SUM(runs)                                           AS team_runs,
    ROUND(SUM(runs) * 100.0 / SUM(SUM(runs)) OVER (), 1) AS pct,
    RANK() OVER (ORDER BY SUM(runs) DESC)               AS team_rank
FROM innings
GROUP BY team;

SUM(SUM(runs)) OVER () = the inner SUM is the GROUP BY aggregate per team, the outer SUM … OVER () adds those team totals (645).

Common mistakes

• ❌ runs * 100 / SUM(runs) OVER () → integer division in SQL Server ✅ use 100.0.
• ❌ Forgetting that AVG skips NULL → ✅ decide: skip (default) or count as 0 with COALESCE(runs, 0).
• ❌ CASE without a NULL branch → ✅ NULL comparisons are never true, so NULLs land in ELSE.

Interview tip

Q: How do you show each row's percentage of the total without a subquery? A: value * 100.0 / SUM(value) OVER () — the window SUM puts the grand total on every row. Use PARTITION BY for "percentage of the group".