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.country, a.population, b.max_pop
FROM countries_by_population a
INNER JOIN (
SELECT rank, MAX(population) as max_pop
GROUP BY rank
) b ON a.rank = b.rank
WHERE a.population > b.max_pop*0.9;
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 */