Hristo Buyukliev Hristo Buyukliev - 6 months ago 8
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?


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 */