Search code examples
sqlinner-joinroundingaverage

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.

Solution

  • 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.