Hristo Buyukliev Hristo Buyukliev - 1 year ago 52
SQL Question

Select countries with population close to mean

I'm doing khanacademy's SQL course, and I'm playing with this dataset. It includes the columns rank (Primary Key), country, population, etc. I want to select countries which are close the global population mean - e.g. if the global population mean is 30m, I want to select countries with 20-40m population.

Now, I tried several things, but none worked. This question is similar, and I tried to modify the answer like this:

SELECT, a.population, b.max_pop
FROM countries_by_population a
SELECT rank, MAX(population) as max_pop
FROM countries_by_population
) b ON a.rank = b.rank
WHERE a.population > b.max_pop*0.9;

However, that returns all rows, and max_pop is just the population for this country. Not working aside, I feel there should be a simpler solution than joining the table with itself. What is it?

Answer Source

The most important difference here is that there's no group by rank.

select *
from countries_by_population
where population / (select avg(population) from countries_by_population) 
    between 0.66 and 1.33 /* approximate range */