What is wrong with this subquery?

Question

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.

It's in invalid aggregation query. I suggest you read my answer here: https://stackoverflow.com/a/13999903/20860 and then read https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html — Bill Karwin, Nov 23 '22 at 22:51
There's no problem with the subquery. The problem is with the main query. — Barmar, Nov 23 '22 at 22:56

score 1 · Accepted Answer · answered Nov 23 '22 at 22:58

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

What is wrong with this subquery?

1 Answers1