AVG() without grouping

Question

In MySQL, I have this Query / Table

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

Now I try to get the avereage rating

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

Because AVG() is an aggregate function, the other lines are stripped. I would like (not like here) to keep the lines like this, without doing another select on my_table, because the WHERE is too complex to do it twice. Like this:

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

I also tried this: Using SUM() without grouping the results but I can't get the CROSS JOIN to work with the table alias or without doing the complex WHERE part twice.

Answer 1

I would use a temporary table.

CREATE TEMPORARY TABLE Ratings (
ID Int Not Null,
Rating Float Not Null
)
Insert Ratings (ID, Rating) 
Select ID, MyFunction(ID)
From my_table
--Where yaadddyaddd

Declare @Average float 
Select @Average = Avg(Rating) From Ratings
Select ID, Rating, @Average

Answer 2

What you're talking about can be done with a simple OAF (ordered analytical function):

SELECT foo.*
    ,AVG(rating) OVER (ORDER BY id ASC) AS rating
FROM my_table AS foo
WHERE bar = /* many crazy shitz */

This will give you the AVG function for the entire table, spread across all rows.