title: MySQL where clause and ordering by avg() as a sub query
body: <p>Although I can group and order by on an aliased sub query, I can't use the alias in a where clause. Do I need to use a join instead?</p>
  
  <p>Works:</p>
  
  <pre><code>SELECT entries.*, 
      (SELECT avg(value) 
      FROM `ratings`
      WHERE ratings.entry_id = entries.id) as avg_rating
  FROM `entries` 
  ORDER BY avg_rating DESC
  </code></pre>
  
  <p>Fails ("unknown column 'avg_rating' in where clause"):</p>
  
  <pre><code>SELECT entries.*, 
      (SELECT avg(value) 
      FROM `ratings` 
      WHERE ratings.entry_id = entries.id) as avg_rating 
  FROM `entries` 
  WHERE avg_rating &lt; '4.5000' ORDER BY avg_rating DESC
  </code></pre>