The problem I am trying to solve is as follows
For each continent show the continent and number of countries with populations of at least 10 million
The world table looks like this:
World (name, continent, area, population, gdp)
My query (which is not returning the correct result):
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
HAVING sum(population) >= 10000000
Query returning the correct result:
SELECT continent, COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent
Can someone tell me why my query is wrong?
The question is about countries, not continents, so you need to do the filtering before aggregation.
Your version of the query is answering:
How many countries are in continents whose population is greater than 10,000,000?
The question is:
How many countries in each continent have a population greater than 10,000,000?
These are different questions. I also realize that for non-fluent English speakers, the difference may not be obvious at first read.