CODEWITHSUNDEEP
mysqlsqldatabase
user1153769
user1153769

Reputation: 23

SQL Query Regarding

I am new to SQL. Currently, I am learning to write complex queries.

I have three tables. Country-- has list of country

-----------+-------------
 CountryId | CountryName
-----------+-------------
         1 | India
         2 | Srilanka
         3 | Pakistan
         4 | Bangladesh
         5 | Nepal
         6 | America
         7 | Japan
         8 | China
         9 | Russia
        10 | Canada
        11 | Australia
---------------------------------------

city - list of cities of country

--------+-------------+-----------
 CityId | CityName    | CountryId
--------+-------------+-----------
      1 | Chennai     |         1
      2 | Washington  |         6
      3 | Moscow      |         9
      4 | Tokyo       |         7
      5 | Beijing     |         8
      6 | Sydney      |        11
      7 | Bangalore   |         1
      8 | Nagercoil   |         1
      9 | AmericaCity |         6
     10 | Kathmandu   |         5
     11 | Dhaka       |         4
     12 | Lahore      |         3
--------------------------------------

Airport - list of airports in a city

 AirportId | AirportName | CityId
-----------+-------------+--------
         1 | Airport1    |      1
         2 | Airport2    |      4
         3 | Airport3    |      5
         4 | Airport4    |      1
         5 | Airport5    |      6
         6 | Airport6    |      3
         7 | Airport7    |      5
         8 | Airport8    |      7
         9 | Airport9    |      6
        10 | Airport10   |      3
        11 | Airport11   |     11
        12 | Airport12   |     10
        13 | Airport13   |     12
---------------------------------

Question: I want to retrieve all the countries with number of airports, like

Output:
countryName Airports
India            3
Srilanka         0
......... etc.

Upvotes: 2

Views: 773

Answers (5)

user359040
user359040

Reputation:

Try:

SELECT
  Country.CountryId,
  Country.CountryName,
  count(AirportID) AS Airports
FROM 
  Country
  LEFT JOIN city ON Country.CountryId=city.CountryId
  LEFT JOIN Airport ON city.CityId=Airport.CityId
GROUP BY Country.CountryId

Upvotes: 0

Matten
Matten

Reputation: 17603

select 
  c.CountryName,
  SUM(
    select count(*) from Airport a
    inner join City city on city.CityId = a.CityId
    where city.CountryId = c.CountryId
    ) as Airports
from
  Country c

Upvotes: 1

Anoop K
Anoop K

Reputation: 56

SELECT CountryName, COUNT(CountryName) AS Airports

FROM Airports INNER JOIN City ON Airports.CityId = City.CityId
              INNER JOIN Country ON City.CountryId = Country.CountryId

GROUP BY CountryId

Hope this will be useful for you

Upvotes: 1

Alex Pliutau
Alex Pliutau

Reputation: 21947

SELECT a.AirportName, co.CountryName, COUNT(co.name) AS count
FROM Airports as a
LEFT JOIN City as c ON a.CityId = c.CityId
LEFT JOIN Country as co ON c.CountryId = co.CountryId
GROUP BY co.CountryId

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65304

SELECT
  Country.CountryName,
  count(*) AS Airports
FROM 
  Country
  INNER JOIN city ON Country.CountryId=city.CountryId
  INNER JOIN Airport ON city.CityId=Airport.CityId
GROUP BY Country.CountryId

Upvotes: 0

Related Questions