CODEWITHSUNDEEP
mysqlsqlaverage
Roman Holzner
Roman Holzner

Reputation: 5928

AVG() without grouping

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.

Upvotes: 1

Views: 2541

Answers (2)

PlantTheIdea
PlantTheIdea

Reputation: 16359

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.

Upvotes: 1

Cheruvian
Cheruvian

Reputation: 5867

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

Upvotes: 2

Related Questions