Search code examples
sqljoinbackendaveragewhere-clause

Find the average from one table and compare it to another

All i want to do is to join two tables, list ALL the rows from the first table, find the average from the second table from all the rows, then list only the ones that are greater than the average. This is wahat i have done so far, and i am only getting one greater than the average but there are others.

SELECT winner_age, AVG(actor_age) FROM oscar_winners
INNER JOIN actors ON actors.id = oscar_winners.id


 WHERE winner_age > (
    
    SELECT AVG(actor_age)
)
Solution

  • You don't really need a join here:

    SELECT o.WINNER_AGE
  FROM OSCAR_WINNERS o
  WHERE o.WINNER_AGE > (SELECT AVG(a.ACTOR_AGE)
                          FROM ACTORS a)