another day and another mysql problem, ive been scratching my head with this question for quite some while now.

My task is through a database called "world" is to retrive and overview of countries with atleast one city, how many cities they have and the average population of these cities. i would also like to sort the average population by using " (AS AverageCityPopulation)" and the number of cities with "(AS NumberOfCities)".

ive just started to learn about join, left join and right join aswell and i am pretty certain that i have to use one of those 3 to complete the task. im still trying to find a helpful way to memorize when to use those 3 (if you have a tip please leave it down below).

anyways, the data should be sorted like this i feel like

of course the data should be displayed sideways but it is a bit hard to make it work in stackoverflow. anyways, i have tried with multiple queries for now, but still havent found the answer. the closest i got to was the entire avg population of a city in Aruba

my current query is:

SELECT 
country.name,
country.code,
city.name,
AVG(city.population) AS averageCityPop,
city.countrycode
FROM

world.city
    right JOIN
world.country ON city.CountryCode = country.code

where city.CountryCode > 1

again i am relativly new, so any thesis or curriculum is appriciated as answers in this post and answers to my question, if you also know any good youtube channels or forums where its helpful to learn mysql it would be great!

thanks for any helpful answers <3

here are a few screenshots about the two tables im trying to connect world.city

world.country

CodePudding user response：

Note that the database I use is MySQL sample database - World. For beginners: both tables have primary keys (for table country, it is 'code', for table city, it is 'id'), so it's enough to use inner joins.

SELECT co.code AS country_code,
       co.name AS country_name, 
       COUNT(*) AS num_cities,
       AVG(ci.population) AS avg_city_pop
FROM country co INNER JOIN city ci ON (co.code = ci.countrycode)
GROUP BY co.code;

Or if you want to show the name of each city:

SELECT co.code AS country_code,
       co.name AS country_name,
       ci.name AS city_name,
       COUNT(*) OVER w AS num_cities,
       AVG(ci.population) OVER w AS avg_city_pop
FROM country co INNER JOIN city ci ON (co.code = ci.countrycode)
WINDOW w AS (PARTITION BY co.code);