title: MySQL: Order by absolute value of user-defined columns
body: <p>Here's a stripped-down version of a query I'm trying to use:</p>
  
  <pre><code>SELECT user_id,
  (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 AND pref = 1) /
  (SELECT COUNT(*) FROM responses WHERE poll_id = 1 AND pref = 1) AS pref_percent,
  (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 ) /
  (SELECT COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent,
  ABS(all_percent - pref_percent) AS deviation
  FROM responses
  GROUP BY user_id
  ORDER BY deviation DESC
  </code></pre>
  
  <p>I get this error: <i>Unknown column 'all_percent' in 'field list'</i></p>
  
  <p>Apparently, I can't reference a user-defined column ("all_percent") when constructing another user-defined column ("deviation").</p>
  
  <p>So, my question is ... how might I modify this query to avoid the error?</p>
  
  <p>I'd really like to have the query results sorted by deviation, rather than having to do that work in PHP.</p>