0

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
    FROM countries_by_population
    GROUP BY rank
) 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?

Community
  • 1
  • 1
Hristo Buyukliev
  • 315
  • 4
  • 8

3 Answers3

0
SELECT 
   a.country,
   a.population
FROM
   countries_by_population a
WHERE
   ABS((a.population - (select AVG(population) from countries_by_population)) / a.population) < .1
Daniel A. Thompson
  • 1,904
  • 1
  • 17
  • 26
0

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

select *
from countries_by_population
where population * 3.000 / (select avg(population) from countries_by_population)
    between 2 and 4
shawnt00
  • 16,443
  • 3
  • 17
  • 22
0

Self joins are very common when working with SQL, don't be afraid of them. In this case you shouldn't need one.

In your query you're grouping by rank, which is the primary key, so of course you're going to get every row. You also aren't actually using AVG() anywhere in your code. You should have a nested query like 

(SELECT AVG(population) from countries_by_population)

which will give you your target number, and then in your where clause you can just directly make comparisons to that query as if it were a number.

Caius
  • 243
  • 1
  • 5