Zeus Zeus - 4 months ago 41
SQL Question

Inner join and average in SQL

I'm new to SQL thus the question. I'm trying to query the following. Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
The table schemas are

City: id, name, countryside, population


Country: code, name,continent, population


I've written the inner join, but can't seem to figure out the way to get the
avg
city population. This is my code.

SELECT COUNTRY.CONTINENT
FROM COUNTRY
INNER JOIN ON
COUNTRY.CODE = CITY.COUNTRYCODE;


Any help appreciated.

Ash Ash
Answer

Ok. Here is the solution.

Select Country.Continent, floor(Avg(city.population))
  From Country 
     Inner Join City
        On Country.Code = City.CountryCode
  Group By Country.Continent;

Here, We have to group by Continent so as to have a result set for continent being the key identifier and then applying AVG function to the population for the cities belonging to this continent.

Comments