Menu
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
275
The MySQL error Invalid use of group function is caused by aggregate functions in your query that's placed in the wrong clause. Most likely you are placing one or more aggregate functions inside the WHERE clause, which won't work because the WHERE clause filters the table before MySQL actually does the computation.
Error code 1111: says invalid use of group function.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
To fix the error above, simply add a quotation mark around the value. You can use both single quotes or double quotes as shown below: INSERT INTO users(username, display_name) VALUES ("jackolantern", 'Jack'); Now the INSERT statement should run without any error.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
