Usage of aggregate function with when self-joining a table

aggregatefunctionsjoin;self-joinsqlite

I want to find people from a database who has a weight bigger then the average weight:

SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)

But I have an error:

Result: misuse of aggregate function avg()

I know that I can write:

SELECT *
FROM Player
WHERE weight > (SELECT AVG(weight)
                FROM Player)

But how can I achieve the same by using self join and without subquery?

Best Answer

Assuming a table like:

CREATE TABLE player
    ( player_id int not null primary key
    , weight int not null
    , nationality char(2) not null -- just any additional attribute
    );

insert into player (player_id, weight, nationality)
values (1,70,'SE'), (2,75,'RU'), (3,60,'US');

The following query appears to work in sqllite (as far as I can tell this is valid SQL99 since p1.weight, p1.nationality is functionally dependent of p1.player_id. Most vendors still implement the more restrictive SQL92 rule for group by). Also note that I prefer ansi join over "," joins, I find them easier to read.

select p1.player_id, p1.weight, p1.nationality
from player p1
cross join player p2
group by p1.player_id
having p1.weight > avg(p2.weight);

having is just syntactic sugar for the chattier:

select player_id, weight, nationality
from (
    select p1.*, avg(p2.weight) as avg_weight
    from player p1
    cross join player p2
    group by p1.player_id
) as t 
where weight > avg_weight;

Another variant is to join against a derived table, pretty much the same query as your original:

select p1.*
from player p1
cross join (
  select avg(weight) as avg_weight
  from player
) as p2 
where p1.weight > p2.avg_weight;

On the other hand you can use window functions to achieve the same thing:

select player_id, weight, nationality
from (
    select p.*
         , avg(weight) OVER () as avg_weight 
    from player p
)
where weight > avg_weight;

You can find an example at: db-fiddle

Related Question