GROUP BY and HAVING

GROUP BY squashes rows that share a value into one row per group, and aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise each group. HAVING then filters the groups, the way WHERE filters rows.

The rule that follows from the order: WHERE can't use aggregates (the groups don't exist yet), and HAVING can (it runs after the grouping). Every selected column must either be in the GROUP BY or inside an aggregate.

The test case

In 2026, which teams averaged more than 160 runs per innings? Show innings played, total runs and the average (one decimal).

Expected result

Leeds averaged 170: champions don't come cheap.

Way 1: HAVING

select t.team_code,
       count(distinct b.match_id)                                 as innings,
       sum(b.total_runs)                                          as runs,
       round(sum(b.total_runs) / count(distinct b.match_id), 1)   as avg_score
from   cr_balls_v b
join   cr_teams t on t.team_id = b.batting_team_id
where  b.season = 2026
group  by t.team_code
having sum(b.total_runs) / count(distinct b.match_id) > 160
order  by runs desc;

A team bats once per match, so count(distinct match_id) is its number of innings. The HAVING repeats the average's formula because it can't see the alias avg_score (it runs before SELECT).

Way 2: aggregate in a subquery, filter with WHERE

select team_code, innings, runs, round(runs / innings, 1) as avg_score
from   (select t.team_code,
               count(distinct b.match_id) as innings,
               sum(b.total_runs)          as runs
        from   cr_balls_v b
        join   cr_teams t on t.team_id = b.batting_team_id
        where  b.season = 2026
        group  by t.team_code)
where  runs / innings > 160
order  by runs desc;

Once the aggregation is inside a subquery, its results are ordinary columns, so a plain WHERE works and the names are available. Many people find this easier to read than a long HAVING.

19c and 26ai

The same on both. On 26ai GROUP BY can also use an alias (page 12.2), but HAVING still can't.

Gotchas

• WHERE for rows, HAVING for groups. where sum(runs) > 900 is an error (ORA-00934). A condition that doesn't involve an aggregate belongs in WHERE: it filters earlier and is cheaper.
• Every non-aggregated column must be grouped. Otherwise ORA-00979: not a GROUP BY expression.
• AVG ignores nulls. The average of 10, null and 20 is 15, not 10.