Как создать такой SQL-запрос?

Question

[Omniverse]

Здравствуйте.

Есть три таблицы:
country

city

countrylanguage


Надо вывести результирующую таблицу типа:


У меня получилось вывести все, кроме количества городов и языков для каждого региона:

SELECT Continent, Region, COUNT(Name) as Countries, ROUND(AVG(LifeExpectancy), 2) as LifeDuration, SUM(Population) as Population
FROM `country`
GROUP BY Region
ORDER BY Continent, Region

Как вывести еще 2 столбца (кол-во городов и языков для каждого региона)?

Answer 1

[nozzy]

select
t3.Continent,
t3.Region,
sum(t3.Countries),
sum(t3.LifeDuration),
sum(t3.Population),
sum(t3.Cities),
sum(t4.Languages)
from
(
select
  t1.Code,
  t1.Continent,
  t1.Region,
  t1.Countries,
  t1.LifeDuration,
  t1.Population,
  t2.Cities
  from
   ( 
     select
     Code,
     Continent,
     Region,
     count(Name) as Countries,
     ROUND(AVG(LifeExpectancy), 2) as LifeDuration, 
     SUM(Population) as Population
     from country
     group by Code,Continent,Region
) t1 left join 
(
  select
  CountryCode,
  count(Name) as Cities
  from city
  group by CountryCode 
) t2 on t2.CountryCode = t1.Code
) t3 left join 
(
  select 
  CountryCode,
  count(Language) as Languages
  from countrylanguage
) t4 on t4.CountryCode = t3.Code

group by t3.Continent,t3.Region

Comments

[Omniverse]

Languages выводятся только NULL, Countries все поля - 1, Cities - неправильные числа выводятся

[Omniverse]

Чтобы Countries правильно выводило я убрал Code из group by, то есть получилось group by Continent,Region. Как сделать Cities и Languages я так и не знаю.

[nozzy]

Code нельзя убирать.
После выполнения моего запроса, у вас уже есть все данные, теперь их нужно сгруппировать (без Code):

select
t3.Continent,
t3.Region,
sum(t3.Countries),
sum(t3.LifeDuration),
sum(t3.Population),
sum(t3.Cities),
sum(t4.Languages)
from
(
select
t1.Code,
t1.Continent,
t1.Region,
t1.Countries,
t1.LifeDuration,
t1.Population,
t2.Cities
from
(
select
Code,
Continent,
Region,
count(Name) as Countries,
ROUND(AVG(LifeExpectancy), 2) as LifeDuration,
SUM(Population) as Population
from country
group by Code,Continent,Region
) t1 left join
(
select
CountryCode,
count(Name) as Cities
from city
group by CountryCode
) t2 on t2.CountryCode = t1.Code
) t3 left join
(
select
CountryCode,
count(Language) as Languages
from countrylanguage
) t4 on t4.CountryCode = t3.Code

group by t3.Continent,t3.Region

[Omniverse]

nozzy: Языки (Languages) все поля, кроме одного, в NULL остались.

[nozzy]

Omniverse:
Надо обернуть в еще один select:

select
t5.Continent,
t5.Region,
sum(t5.Countries),
sum(t5.LifeDuration),
sum(t5.Population),
sum(t5.Cities),
sum(t5.Languages)
from
(

select
t3.Code
t3.Continent,
t3.Region,
t3.Countries,
t3.LifeDuration,
t3.Population,
t3.Cities,
t4.Languages
from
(
select
t1.Code,
t1.Continent,
t1.Region,
t1.Countries,
t1.LifeDuration,
t1.Population,
t2.Cities
from
(
select
Code,
Continent,
Region,
count(Name) as Countries,
ROUND(AVG(LifeExpectancy), 2) as LifeDuration,
SUM(Population) as Population
from country
group by Code,Continent,Region
) t1 left join
(
select
CountryCode,
count(Name) as Cities
from city
group by CountryCode
) t2 on t2.CountryCode = t1.Code
) t3 left join
(
select
CountryCode,
count(Language) as Languages
from countrylanguage
) t4 on t4.CountryCode = t3.Code
) t5
group by t5.Continent,t5.Region

[Omniverse]

Всё равно с языками та же проблема...

[Omniverse]

Что интересно, все ячейки в столбце Languages принимают значение NULL, кроме одной, в этой одной - значение 984 - которое является общим количеством языков в таблице countrylanguage.

[nozzy]

Omniverse:
Опечатка, нужно добавить group by:

select
CountryCode,
count(Language) as Languages
from countrylanguage
group by CountryCode <------- добавить
) t4 .....

[Omniverse]

Да, теперь работает.
Большое спасибо!

Answer 2

[Ihor Gevorkyan]

Добрый день, попробуйте посмотреть в сторону JOIN-ов.

Answer 3

[Omniverse]

Пробовал сделать такой запрос с join'ами:

SELECT a.Continent, a.Region, COUNT(a.Name) as Countries, 
       ROUND(AVG(a.LifeExpectancy), 2) as LifeDuration, SUM(a.Population) as Population, 
       COUNT(b.name) as cities, COUNT(c.Language) as Languages 
FROM `country` as a 
LEFT JOIN city as b 
ON a.Code = b.CountryCode 
LEFT JOIN countrylanguage as c 
ON a.Code = c.CountryCode 
GROUP BY a.Region 
ORDER BY a.Continent, a.Region

Но выводится такая ерунда:


По сути я знаю как по отдельности в 3 запроса вывести все столбцы:
Continent, Region, Countries, LifeDuration, Population:

SELECT Continent, Region, COUNT(Name) as Countries, ROUND(AVG(LifeExpectancy), 2) as LifeDuration, SUM(Population) as Population
FROM `country`
GROUP BY Region
ORDER BY Continent, Region

Cities:

SELECT COUNT(b.name) as cities 
FROM `country` as a
LEFT JOIN city as b
ON a.Code = b.CountryCode
GROUP BY a.Region
ORDER BY a.Continent, a.Region

Languages:

SELECT COUNT(c.Language) as Languages 
FROM `country` as a
LEFT JOIN countrylanguage as c
ON a.Code = c.CountryCode
GROUP BY a.Region
ORDER BY a.Continent, a.Region

Как объединить эти три запроса в один, чтоб вывелась результирующая таблица?