mysql query to calculate cricket batsman average

I have a mysql table , which holds data like this.

Name    |how_out    |runs
------------------------------
Robin   |Not out    |45
Robin   |lbw        |25
Robin   |caught     |50
开发者_开发技巧Robin   |caught     |0
Robin   |bowled     |30
Robin   |bowled     |0
.
.
.

I want one SQL query, which returns the following columns

Name , Total_runs , Average

Sounds simple enough, but the only problem for me is that. In cricket , average is just not Sum( runs)/count(*) it's Sum(runs)/Count of number of innings where how_out is not 'Not out'

Could some one tell me how to write one such query ?

The query that i wrote gave the wrong average for obvious reasons. The query that i wrote was

Select `Name`,sum(`runs`) as 'Total_runs', Sum(`runs`)/count(*)
From `batsman_stats`
group by `Name`
order by 2 desc

Could sql experts please help me ?

Thanks

---

Select `Name`,sum(`runs`) as 'Total_runs', Sum(`runs`)/sum(if(how_out <> 'Not out',1,0))
From `batsman_stats`
group by `Name`
order by 2 desc

---

I think you need a subselect:

Select Name, sum(runs) as Total_runs, Sum(runs)/(
   select count(*) from batsman_stats b
   where b.Name=a.Name and not b.how_out = 'Not out'
)
From batsman_stats a
group by Name
order by 2 desc

---

You could use a CASE statement:

SELECT Name,
       SUM(Runs) as Total_runs, 
       SUM(Runs)/COUNT(CASE WHEN how_out <> 'Not out' THEN 1 END) as Average
FROM batsman_stats
GROUP BY Name
ORDER BY SUM(Runs) DESC