Practice: two turns

select team_code, caught, bowled, lbw, run_out, stumped,
       caught + bowled + lbw + run_out + stumped as total
from   (select t.team_code, b.wicket_kind
        from   cr_balls_v b
        join   cr_teams t on t.team_id = b.bowling_team_id
        where  b.wicket_kind is not null)
pivot  (count(*) for wicket_kind in ('caught' as caught, 'bowled' as bowled, 'lbw' as lbw,
                                     'run out' as run_out, 'stumped' as stumped))
order  by total desc, team_code;

select t.team_code,
       count(case when b.wicket_kind = 'caught'  then 1 end) as caught,
       count(case when b.wicket_kind = 'bowled'  then 1 end) as bowled,
       count(case when b.wicket_kind = 'lbw'     then 1 end) as lbw,
       count(case when b.wicket_kind = 'run out' then 1 end) as run_out,
       count(case when b.wicket_kind = 'stumped' then 1 end) as stumped,
       count(b.wicket_kind)                                  as total
from   cr_balls_v b
join   cr_teams t on t.team_id = b.bowling_team_id
group  by t.team_code
order  by total desc, t.team_code;

COUNT(*) in a pivot counts the rows for each value, and gives 0 (not null) when there are none.

select *
from   (select over_no, innings_no, total_runs from cr_balls_v where match_id = 32 and over_no <= 6)
pivot  (sum(total_runs) for innings_no in (1 as first_innings, 2 as second_innings))
order  by over_no;

select over_no,
       sum(case when innings_no = 1 then total_runs end) as first_innings,
       sum(case when innings_no = 2 then total_runs end) as second_innings
from   cr_balls_v
where  match_id = 32 and over_no <= 6
group  by over_no
order  by over_no;

Numbers work in the IN list too: IN (1 AS first_innings, 2 AS second_innings).