SELECT name, countrycode, population, avg(population)
FROM city
WHERE population > (SELECT avg(population) FROM city);

I need to figure out what is wrong with this subquery. Personally I think it is something to do with the placement of avg(population) but I dont know exactly.

CodePudding user response：

When you use an aggregation function without GROUP BY, it aggregates the entire table into a single result row. Then it makes no sense to include non-aggregated columns in the SELECT list, because it will just be one value from arbitrary rows in the table.

If you want to include the average over the entire table along with the selected rows, join with the subquery so you can refer to it in the SELECT list, instead of using the subquery in the WHERE clause.

SELECT name, countrycode, population, avg_population
FROM city
JOIN (
    SELECT avg(population) AS avg_population
    FROM city
) AS x ON population > avg_population