MySQL 错误代码 1111。组函数的使用无效

Error Code 1111. Invalid use of group function

提问by user3374108

So this works:

所以这有效：

SELECT c.name AS country_name, c.population AS country_population, SUM(ci.population) AS city_population, ROUND(100*(SUM(ci.population)/c.population)) AS city_population_percent
FROM country AS c
JOIN city AS ci
ON c.code = ci.countrycode
WHERE c.continent = 'Europe'
GROUP BY c.name

But I need to only grab the city_population_percent values greater than 30, so I try this:

但我只需要获取大于 30 的 city_population_percent 值，所以我试试这个：

SELECT c.name AS country_name, c.population AS country_population, SUM(ci.population) AS city_population, ROUND(100*(SUM(ci.population)/c.population)) AS city_population_percent
FROM country AS c
JOIN city AS ci
ON c.code = ci.countrycode
WHERE c.continent = 'Europe'
**AND ROUND(100*(SUM(ci.population)/c.population)) > 30**
GROUP BY c.name

And that's when I get:

那是我得到的时候：

Error Code 1111. Invalid use of group function

错误代码 1111。组函数的使用无效

That is, it fails when I add this condition in the WHERE:

也就是说，当我在以下内容中添加此条件时它会失败WHERE：

AND ROUND(100*(SUM(ci.population)/c.population)) > 30

回答by Surabhil Sergy

So you have to move this condition to the HAVINGclause

所以你必须把这个条件移到HAVING子句

SELECT c.name AS country_name, c.population AS country_population, SUM(ci.population) AS city_population, ROUND(100*(SUM(ci.population)/c.population)) AS city_population_percent
            FROM country AS c
            JOIN city AS ci
            ON c.code = ci.countrycode
WHERE c.continent = 'Europe'
GROUP BY c.name
HAVING ROUND(100*(SUM(ci.population)/c.population)) > 30

回答by nos

You're using aggregate functions in a where clause, something you cannot do in SQL.

您在 where 子句中使用聚合函数，这是您在 SQL 中无法做到的。

Use the HAVINGclause instead:

请改用HAVING子句：

WHERE c.continent = 'Europe'
GROUP BY c.name
HAVING ROUND(100*(SUM(ci.population)/c.population)) > 30