Error Code 1111. Invalid use of group function

Question

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:

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

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

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

In addition to using the `having` clause: your general usage of `group by` is invalid in (standard) SQL. MySQL's sloppy implementation of the `group by` allows something that would fail in any other DBMS. For more details please read this: http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/ and this: http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html — , Mar 03 '14 at 08:41

score 44 · Accepted Answer · answered Mar 03 '14 at 08:37

44

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

answered Mar 03 '14 at 08:37

Surabhil Sergy

1,946
1
23
40

3

Don't forget that this invalid use of `group by` will just return random data that might no be the correct result. – Mar 03 '14 at 08:42

score 7 · Answer 2 · edited May 23 '17 at 12:09

7

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

Use the HAVING clause instead:

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

edited May 23 '17 at 12:09

Community

1
1

answered Mar 03 '14 at 08:37

nos

223,662
58
417
506

Error Code 1111. Invalid use of group function

2 Answers2

Linked