SQL dealing with subqueries Error Code: 1111

Question

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 );

Answer 1

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 );