SQL: Using AVG() in a Greater or Lesser Then to only return those rows

Question

SELECT name, population
FROM countries 
WHERE population > AVG(population);

In case more additions are added, I cannot keep the AVG of the population as a number. This won't run as I get the error:

misuse of aggregate function AVG()

question from:https://stackoverflow.com/questions/65925328/sql-using-avg-in-a-greater-or-lesser-then-to-only-return-those-rows

Answer 1

yes , you can't use aggregate function in where like that.it should be used in a sub-query for your solution/ here is how you can do it,

SELECT name, population
FROM countries 
WHERE population > ( select AVG(population) FROM countries) ;

you also can populate the avg population in a variable:

declare @avgpop as decimal 
select @avgpop= AVG(population) FROM countries;

SELECT name, population
FROM countries 
WHERE population > @avgpop ;