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

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:

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

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


Assuming a table like:

    ( 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

| improve this answer | |

This is a good example case for using the WITH clause, also known as CTEs - or COMMON TABLE EXPRESSIONS. Having said that, there is nothing wrong with your working query!

There's no way you're going to avoid using an aggregate here - an average has to be calculated, but below is, what I hope, a good example of using a CTE - even if it's not required in this case.

In fact, CTEs are never absolutely required, they're just an elegant shorthand that can take subqueries out of the body of the main query and really help readability and thereby reduce bugs (and stress!).

To address this issue, I did the following (dbfiddle):


Then some simple data:

INSERT INTO player VALUES (1, 60), (2, 70), (3, 80), (4, 90);

Then, the query:

  SELECT AVG(weight) AS w_avg FROM player
SELECT * FROM player WHERE weight > (SELECT w_avg FROM wa);

which gives (correct) result:

id  weight
 3      80
 4      90

CTEs are a really powerful way of simplifying queries and are well worth putting into your toolbox! p.s. welcome to the forum! :-)

| improve this answer | |
  • Is it possible to do it only using one SELECT statement? Probable with self-join like the first query in the question? – Kenenbek Arzymatov Jun 2 '19 at 8:03
  • Nope - I don't think so! You have a choice of your original working query or mine. You have to get an average and then compare it with every player's weight. I can't see any way of doing it in the manner you suggest! Though it would be kind of cool if your notation could be made to work - a suggestion for the ISO SQL committee perhaps :-) - but then, I'm sure this must have been considered? – Vérace Jun 2 '19 at 8:19
  • @RomaKarageorgievich, you can use having instead of where or add a nesting level. – Lennart Jun 2 '19 at 19:20

If you really want to avoid a subquery for some reason, and if you're using a recent version of sqlite (3.25 or newer), you can use a window function to calculate if each row is greater than the average, and check that when you're iterating over the rows returned by the query, but it won't actually filter out the rows that aren't greater from the results:

SELECT *, weight > avg(weight) OVER () AS bigger_than_average FROM Player;

The bigger_than_average column will be either 1 or 0 depending on the condition.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.