CODEWITHSUNDEEP
mysqlmysql-workbench
IronBat
IronBat

Reputation: 107

count using two tables in and display 0 for no result SQL

I have been struggling and would really appreciate some assistance:

I have two tables flights and carriers.

flights 

Flight_ID | Airport_Origin | Airport_Destination | Carrier_ID 
1 | DOM | IAD | 1
2 | IAD | DOM | 4
3 | BAL | UEI | 1
4 | LAX | CHI | 2
5 | VAS | SDA | 3
6 | MUM | PAR | 2
7 | LAD | BOS | 4

carriers

carrier_ID | Carrier_Name
1 | American 
2 | southwest
3 | delta
4 | united
5 | spirit

I would like to display each airline carrier by its name with a count of number of flights. And if the airline has no flights, it should show 0 and should display all carriers.

Output should be:

CarrierName | NumberofFlights
American    | 2
southwest   | 2
delta       | 1
united      | 2
spirit      | 0

my code:

select carriers.Carrier_Name AS 'Carrier Name', count(*) AS 'Number of Flights'
from flights 
inner join carriers
on flights.Carrier_ID = carriers.Carrier_ID
group by Carrier_Name 
Order BY Carrier_Name
;

I cannot seem to show the carrier with no flights as 0. My output is as follows:

enter image description here

Upvotes: 1

Views: 75

Answers (2)

IVNSTN
IVNSTN

Reputation: 9318

That is a LEFT JOIN (outer join) when records from right part of join may not exist:

select
  c.Carrier_Name AS 'Carrier Name', count(f.Flight_ID) AS 'Number of Flights'
from carriers c
left join flights f on f.Carrier_ID = c.Carrier_ID
group by Carrier_Name 
order by Carrier_Name

also this can be accomplished by such a subquery:

select
  c.Carrier_Name AS 'Carrier Name', 
  (select count(*) from flights f where f.Carrier_ID = c.Carrier_ID)  AS 'Number of Flights'
from carriers c
order by Carrier_Name

INNER JOIN means that corresponding records from both parts of join must exist.

Upvotes: 0

Barmar
Barmar

Reputation: 781741

You need to use an outer join so the result will include rows with no match. And then the COUNT function has to count a row in the flights table so it won't count the null row that results when there's no match.

SELECT c.carrier_name AS `Carrier Name`, COUNT(f.Flight_ID) AS `Number of Flights`
FROM carriers AS c
LEFT JOIN flights AS f ON f.carrier_ID = c.carrier_ID
GROUP BY c.carrier_name
ORDER BY c.carrier_name

Upvotes: 1

Related Questions