mysql - Top 10 players with the highest batting average

Question

Batting average = total number of runs scored / number of times out.

Here we need to make sure to include run outs (on non-striker end)

Output like :

Batsman_name     Average

KL Rahul            44

Error:

Error in SQL statement: AnalysisException: cannot resolve 'batsman_runs' given input columns: [_auto_generated_subquery_name.Batsman]; line 1 pos 21; 'Sort ['Average DESC NULLS LAST], true

select Batsman_, sum(batsman_runs)/count(player_dismissed) as Average 
from
(
 (select batsman as Batsman_ from IPL_BALL_BY_BALL) 
 union all 
 (select non_striker as Batsman_ from IPL_BALL_BY_BALL)
)
group by Batsman_ 
order by Average desc;

Answer 1

because from your subquery ( result of union) you only returning "Batsman_" column. you have to retunr all the column you need :

select Batsman_, sum(batsman_runs)/count(player_dismissed) as Average 
from
(
 (select batsman as Batsman_,batsman_runs,player_dismissed from IPL_BALL_BY_BALL) 
 union all 
 (select non_striker as Batsman_,batsman_runs,player_dismissed from IPL_BALL_BY_BALL)
)
group by Batsman_ 
order by Average desc;