Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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: mysqlperformanceblog.com/2006/09/06/… and this: rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
– a_horse_with_no_name
                Mar 3, 2014 at 8:41
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
                Don't forget that this invalid use of group by will just return random data that might no be the correct result.
– a_horse_with_no_name
                Mar 3, 2014 at 8:42
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.