2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sql_learner
  • 537
  • 2
  • 8

2 Answers2

4

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I remove the `HAVING sum(population) >= 10000000` and execute the result of the query is same as that of keeping `HAVING sum(population) >= 10000000` and executing.Does it implies that there is no effect of `HAVING sum(population) >= 10000000` – sql_learner Oct 21 '18 at 12:56
  • 1
    That simply means that all continents (with countries) have a population of at least 10,000,000. – Gordon Linoff Oct 21 '18 at 13:04
  • My english seems to be terrible.Thanks for your patience though :) – sql_learner Oct 21 '18 at 15:14
0

The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.

Saurabh
  • 76
  • 5