错误代码1111.无效使用组功能 [英] Error Code 1111. Invalid use of group function

问题描述

这可行:

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

但是我只需要获取大于30的city_population_percent值，所以我尝试这样做:

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

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

那是我得到的时候:

错误代码1111.组功能的无效使用

Error Code 1111. Invalid use of group function

也就是说，当我在WHERE中添加此条件时，它会失败:

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

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

推荐答案

因此，您必须将此条件移至 HAVING 子句

So you have to move this condition to the HAVING clause

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

这篇关于错误代码1111.无效使用组功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！