MySQL error 1242: Subquery returns more than 1 row

Question

I'm working on some SQL homework, and I've come to a dead-end on this one question and I'm hoping someone can point out what exactly I'm doing wrong here.

SELECT Name,
    (SELECT Name
    FROM City
    WHERE City.CountryCode = Country.Code) AS 'city',

    (SELECT Population
    FROM City
    WHERE City.CountryCode = Country.Code) AS 'city_population'
FROM Country
WHERE Region IN ('Western Europe')
HAVING city_population > (SUM(Population) / COUNT(city))
ORDER BY Name, city;

What I'm trying to do here is retrieve from a database of global statistics a list of cities (from the City table) matched with their Country from that table, in which the country is in the region of Western Europe and the population of the city is greater than the average population of cities for its country, ordered by country and city name. The CountryCode and Code are the keys for the tables.

Can anyone tell me where I'm going wrong? I'm guessing MySQL is unhappy because my subqueries are returning more rows than the selector for country names does, but that's exactly what I want to do. I want multiple rows for a country value, one row for each city that meets the search criteria of having greater than average populations. The assignment also specifically forbids me from using joins to solve this problem.

Answer 1

A join should do it. You can join city on country code, and filter out cities that have a lower than average population

select
  co.Name as CountryName,
  ci.Name as CityName,
  ci.Population as CityPopulation
from
  Country co
inner join City ci 
  on ci.CountryCode = co.CountryCode 
where
  co.Region in ('Western Europe')
  and ci.Population > 
    (select sum(ca.Population) / count(*) from City ca 
     where ca.CountryCode = co.CountryCode)

Additions: Since you are not allowed to use joins, you could solve it in a couple of ways.

1) You can alter your query a little bit, but it won't return rows for each city. Instead it will return the list of cities as a single field. This is only a slight modification of your query. Note the GROUP_CONCAT function, which works like SUM only it concats the values instead of summing them. Also note the added ORDER BY clause in the subselects, so you can make sure The nth Population matches the nth City name.

SELECT Name,
    (SELECT GROUP_CONCAT(Name)
    FROM City
    WHERE City.CountryCode = Country.Code
    ORDER BY City.Name) AS 'city',

    (SELECT GROUP_CONCAT(Population)
    FROM City
    WHERE City.CountryCode = Country.Code
    ORDER BY City.Name) AS 'city_population'
FROM Country
WHERE Region IN ('Western Europe')
HAVING city_population > (SUM(Population) / COUNT(city))
ORDER BY Name, city;

2) You can alter by query a little bit. Remove the join on Country, and instead use some subselects in the filter and in the select. The latter is only needed if you need country name at all. If country code is enough, you can select that from City.

select
  (select County.Name 
   from Country 
   where County.CountyCode = ci.CountryCode) as CountryName,
  ci.CountryCode,
  ci.Name as CityName,
  ci.Population
from
  City ci 
where
  -- Select only cities in these countries.
  ci.CountryCode in
    ( select co.CountryCode 
      from Country co
      where co.Region in ('Western Europe'))
  -- Select only cities of above avarage population.
  -- This is the same subselect that existed in the join before, 
  -- except it matches on CountryCode of the other 'instance' of 
  -- of the City table. Note, you will _need_ to use aliases (ca/ci)
  -- here to make it work.
  and ci.Population > 
    ( select sum(ca.Population) / count(*) 
      from City ca 
      where ca.CountryCode = ci.CountryCode)

Answer 2

A subquery in the the select part of the statement only expects one value returned from the query. Remember the commas that separate the values in your select statement represent columns and each column expects one value. In order to get a list of values returned in a subquery (as if it were another table) and use it in the outer query you would have to put the subqueries in the from part of your Query. Note: this may not be a proper code for your results. I was just addressing the issue of the MySQL error 1242.

SELECT Name   
FROM Country, (SELECT Name
    FROM City
    WHERE City.CountryCode = Country.Code) AS 'city',

    (SELECT Population
    FROM City
    WHERE City.CountryCode = Country.Code) AS 'city_population'
WHERE Region IN ('Western Europe')
HAVING city_population > (SUM(Population) / COUNT(city))
ORDER BY Name, city;