Find the average from one table and compare it to another-CodePudding

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)
)

CodePudding user response：

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)

CodePudding user response：

Something like this?

SELECT actors.*, (SELECT AVG(actor_age) from actors) as average
FROM oscar_winners
INNER JOIN actors ON actors.id = oscar_winners.id and actors.winner_age > (SELECT AVG(actor_age) from actors)

CodePudding user response：

The problem with your query is because you are using a where clause, while you should probably be using having:

SELECT w.winner_age, AVG(a.actor_age) 
FROM oscar_winners w
INNER JOIN actors a
ON actors.id = oscar_winners.id
group by w.winner_age
having w.winner_age > AVG(a.actor_age)