Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
397 views
in Technique[技术] by (71.8m points)

mysql - Top 10 players with the highest batting average

1

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;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...