title: AVG() without grouping
body: <p>In MySQL, I have this Query / Table</p>
  
  <pre><code>SELECT * 
  FROM
  (
      SELECT
          id,
          (SELECT MY_FUNCTION(id)) AS rating -- Function returns a float
      FROM my_table
      WHERE
      /* Very long WHERE */
  ) AS ratings
  
  id              rating
  ----------      -------------
  1               1.00
  2               2.00
  3               10.00
  4               11.05
  5               10.04
  </code></pre>
  
  <p>Now I try to get the avereage rating</p>
  
  <pre><code>SELECT *, AVG(rating)
  FROM
  (
      SELECT
          id,
          (SELECT MY_FUNCTION(id)) AS rating -- Function returns a float
      FROM my_table
      WHERE
      /* Very long WHERE */
  ) AS ratings
  
  id              rating          AVG(rating)
  ----------      -------------   -------------
  1               1.00            6,818
  </code></pre>
  
  <p>Because AVG() is an aggregate function, the other lines are stripped. I would like (not like <a href="https://stackoverflow.com/questions/11357640/using-sum-without-grouping-the-results">here</a>) to keep the lines like this, <strong>without</strong> doing another select on <code>my_table</code>, because the WHERE is too complex to do it twice. Like this:</p>
  
  <pre><code>id              rating          AVG(rating)
  ----------      -------------   -------------
  1               1.00            6,818
  2               2.00            6,818
  3               10.00           6,818
  4               11.05           6,818
  5               10.04           6,818
  </code></pre>
  
  <p>I also tried this: <a href="https://stackoverflow.com/questions/11357640/using-sum-without-grouping-the-results">Using SUM() without grouping the results</a> but I can't get the CROSS JOIN to work with the table alias or without doing the complex WHERE part twice.</p>