Practice: three aggregation problems

select p.player_name,
       sum(b.is_legal)                                                                        as balls,
       sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end) as runs,
       sum(b.is_bowler_wicket)                                                                as wickets,
       round(6 * sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end)
             / sum(b.is_legal), 2)                                                            as economy
from   cr_balls_v b
join   cr_players p on p.player_id = b.bowler_id
where  b.season = 2026
group  by p.player_name
having sum(b.is_legal) >= 72
order  by economy, p.player_name;

select player_name, balls, runs, wickets, round(6 * runs / balls, 2) as economy
from   (select p.player_name,
               sum(o.balls)   as balls,
               sum(o.runs)    as runs,
               sum(o.wickets) as wickets
        from   (select bowler_id, match_id, over_no,
                       sum(is_legal) as balls,
                       sum(runs_batter + case when extra_type in ('WD', 'NB') then runs_extras else 0 end) as runs,
                       sum(is_bowler_wicket) as wickets
                from   cr_balls_v
                where  season = 2026
                group  by bowler_id, match_id, over_no) o
        join   cr_players p on p.player_id = o.bowler_id
        group  by p.player_name)
where  balls >= 72
order  by economy, player_name;

Count legal balls rather than overs: the last over of a chase (or of an all-out innings) can stop after two or three balls, and counting it as a whole over would flatter the bowler. The second answer aggregates twice, balls into overs and overs into bowlers, which gives the same totals.

select b.season,
       case when grouping(b.phase) = 1 then 'All phases' else b.phase end as phase,
       sum(b.is_four) as fours,
       sum(b.is_six)  as sixes
from   (select season, is_four, is_six,
               case when over_no <= 6 then 'Powerplay' when over_no >= 17 then 'Death' else 'Middle' end as phase
        from   cr_balls_v) b
group  by b.season, rollup (b.phase)
order  by b.season, grouping(b.phase), b.phase;

select b.season,
       case when grouping(b.phase) = 1 then 'All phases' else b.phase end as phase,
       sum(b.is_four) as fours,
       sum(b.is_six)  as sixes
from   (select season, is_four, is_six,
               case when over_no <= 6 then 'Powerplay' when over_no >= 17 then 'Death' else 'Middle' end as phase
        from   cr_balls_v) b
group  by grouping sets ((b.season, b.phase), (b.season))
order  by b.season, grouping(b.phase), b.phase;

(The b. prefixes avoid the alias trap from page 3.1.) GROUP BY season, ROLLUP (phase) is a partial rollup: season is always grouped, phase is rolled up within it, so there's no grand total row.

select toss_decision,
       count(*)                                                      as times,
       count(case when winner_id = toss_winner_id then 1 end)        as toss_winner_won
from   cr_matches
group  by toss_decision
order  by toss_decision;

select toss_decision,
       count(*)                                                       as times,
       sum(case when winner_id = toss_winner_id then 1 else 0 end)    as toss_winner_won
from   cr_matches
group  by toss_decision
order  by toss_decision;