CODEWITHSUNDEEP
mysqlsqlmysql-error-1111
Genghis Conn
Genghis Conn

Reputation: 51

SQL dealing with subqueries Error Code: 1111

enter image description here

I'm trying to learn some SQL, so I've been messing around with the sample world schema. For this query, the goal is, for each continent, to retrieve the capitals of the countries in which life expectancy is greater than the average for that continent.

The code below is my attempt at that, but I receive an 1111 error on the where statement. any help would be much appreciated!

SELECT continent AS Continent,
       city.name AS Capital
FROM city,
     country c1
WHERE c1.capital = city.id
  AND avg(LifeExpectancy) <
    (SELECT avg(LifeExpectancy)
     FROM country c2
     WHERE c2.continent = c1.continent );

Upvotes: 0

Views: 349

Answers (2)

Somy
Somy

Reputation: 1624

Based on your problem statement, you would not need avg life expectancy of the capital, just the life expectancy of the country corresponding to the capital greater than the average for that continent. So, I made those adjustments in the query below.

Let me know this works.

SELECT continent AS Continent,
       city.name AS Capital
FROM city,
     country c1
WHERE c1.capital = city.id
  AND c1.LifeExpectancy >
    (SELECT avg(LifeExpectancy)
     FROM country c2
     WHERE c2.continent = c1.continent );

Upvotes: 1

forpas
forpas

Reputation: 164089

You must join properly the tables country and city to a query that returns the average lifeexpectancy for each continent:

select co.code, ci.name
from country co
inner join city ci on ci.id = co.capital
inner join (
  select continent, avg(lifeexpectancy) avg_lifeexpectancy
  from country
  group by continent
) t on t.continent = co.continent and t.avg_lifeexpectancy < co.lifeexpectancy

Upvotes: 1

Related Questions