World database exercise

Question

European city which population is closest for defect to the median of European population?

I try with this code, but I always have an error with the where condition in the subquery

Select city.name,city.population from city
join country
on city.countrycode=country.code and country.continent='europe'
where city.population<(
avg(country.population)
where country.continent='europe')
order by city.population desc;

Put SELECT before `avg(country.population)`. That is a subquery and need to be started with SELECT. — leonardo_palma, Nov 28 '15 at 14:05
Possible duplicate of [Simple way to calculate median with MySQL](http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) — Strawberry, Nov 28 '15 at 14:56
Ok, i find out the solution, my mistake was repeating the where clause meanwhile i already declared it in the join! — Federico Cosentino, Nov 29 '15 at 09:57

score 1 · Answer 1 · answered Nov 28 '15 at 14:14

1

Select city.name,city.population from city
join country
on city.countrycode=country.code and country.continent='europe'
where city.population < (select avg(country.population)
where country.continent='europe')
order by city.population desc;

answered Nov 28 '15 at 14:14

Saba Shafi

31
3

I cannot see that this answer addresses the question. – Strawberry Nov 28 '15 at 15:00

score 0 · Answer 2 · answered Nov 29 '15 at 10:05

Ok, i find out the solution, my mistake was repeating the where clause meanwhile i already declared it in the join:

Select city.name,city.population from city
Join country
On city.countrycode=country.code and country.continent=’europe’
Where (select avg(country.population))>city.population
Order by city.population desc
Limit 1;

World database exercise

2 Answers2