title: mysql query to calculate cricket batsman average
body: <p>I have a mysql table , which holds data like this.</p>
  
  <pre><code>Name    |how_out    |runs
  ------------------------------
  Robin   |Not out    |45
  Robin   |lbw        |25
  Robin   |caught     |50
  Robin   |caught     |0
  Robin   |bowled     |30
  Robin   |bowled     |0
  .
  .
  .
  </code></pre>
  
  <p>I want one SQL query, which returns the following columns</p>
  
  <p>Name , Total_runs , Average</p>
  
  <p>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 <code>how_out</code> is not 'Not out'</p>
  
  <p>Could some one tell me how to write one such query ?</p>
  
  <p>The query that i wrote gave the wrong average for obvious reasons.
  The query that i wrote was</p>
  
  <pre><code>Select `Name`,sum(`runs`) as 'Total_runs', Sum(`runs`)/count(*)
  From `batsman_stats`
  group by `Name`
  order by 2 desc
  </code></pre>
  
  <p>Could sql experts please help me ?</p>
  
  <p>Thanks</p>