CODEWITHSUNDEEP
sql
Kara
Kara

Reputation: 785

SQL: Compare avg to overall avg

I have one table called 'ratings'

ID    RATING
1       5
1       2
2       5
3       1
3       4
3       4

And i want to find the average rating of each restaurant (same id) that is greater than or equal to than the overall average rating (avg rating of all the restaurants combined)

So for example, restaurant ID 1 avg would be 3.5 and restaurant ID 3 would be 3. The overall avg in this case is a 3.5 So the table should return

ID   RATING
1      3.5
2      5

This is how I did it so far but I'm not sure how to compare it to the overall average.

SELECT x.id, AVG(x.rating) AS average
FROM ratings
GROUP BY x.rid;

So this returns a table with the average ratings for each restaurant ID, but How do i compare this to the total average of ratings without hardcoding it?

Upvotes: 1

Views: 3589

Answers (4)

Thomas Decaux
Thomas Decaux

Reputation: 22661

This is a good use-case for windowed function!

avg(rating) over () as global_avg will return the overall average as a new column.

Here the solution, using sub queries as well to "split steps":

select * from (
    select id, avg(rating) as user_avg, global_avg
        from (
            select id, rating,  avg(rating) over () as global_avg
            from notes
        )
    group by id, global_avg
)
where user_avg >= global_avg
order by id

This is powerful, you could add a new column "country" and calculate the avg by country:

+---+------+-------+
| id|rating|country|
+---+------+-------+
|  1|     5|     fr|
|  1|     2|     fr|
|  2|     5|     it|
|  3|     1|     it|
|  3|     4|     it|
|  3|     4|     it|
|  4|     2|     fr|
+---+------+-------+

select * from (
    select id, country, avg(rating) as user_avg, country_avg
        from (
            select id, country, rating, avg(rating) over (partition by country) as country_avg
            from notes
        )
    group by id, country, country_avg
)
where user_avg >= country_avg
order by id

Upvotes: 2

Erik Blessman
Erik Blessman

Reputation: 693

If you want the average of the selected ids to be greater than the average of the other ids, you have to take the average of the averages first, so that the average you're comparing each individual average to isn't skewed by one id getting a lot more reported ratings our a lot fewer reported ratings.

e.g. - id:1 gets 1000 ratings of 1, id:2 gets 1 rating of 2, id:3 gets one rating of 3 and id:4 gets 1 rating of 4. In this case, if you just take the average of all ratings, it is slightly more than 1 (this would show ids 2, 3 & 4 as having average ratings higher than 1.x. If you average the ratings for each id first, each id coincidentally has an average rating equal to their id. The average of these ratings is 2.5. This would show that only ids 3 & 4 have a higher average.

SELECT id
,      rating
FROM   (
    SELECT x.id
    ,      AVERAGE(x.rating) AS rating
    FROM   ratings
    GROUP BY x.id
) avgRatings
WHERE  rating >= (SELECT AVERAGE(rating) FROM avgRatings)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would be inclined to use window functions:

select r.*
from (select r.id, avg(rating*1.0)) as avgr,
             sum(sum(rating*1.0)) over () / sum(count(*)) over () as avgavg
      from ratings r
      group by r.id
     ) r
where avgr >= avgavg

Upvotes: 0

void
void

Reputation: 7880

use having clause:

SELECT x.id, AVG(x.rating) AS average
FROM ratings x
GROUP BY x.rid
Having AVG(x.rating)>(select AVG(rating) from ratings);

Upvotes: 3

Related Questions