SQL sub query counting numbers

Question

SELECT b.PlateNumber, BusModel, Count(DISTINCT d.StaffID) AS NumberOfDirvers
FROM Bus b, Trip t, Driver d, Staff s
WHERE b.PlateNumber = t.PlateNumber 
AND t.StaffID = d.StaffID
AND d.StaffID = s.StaffID
AND b.capacity = 72
AND EXTRACT(MONTH FROM s.DateOfBirth) BETWEEN '07' AND '12'
GROUP BY PlateNumber, BusModel;

apparently there are some bus that hasn't got any trip with any drivers yet, but my code can't seem to show number of drivers as 0. how do i show it?

Question:

Given a bus capacity, say 72, find the set of buses that have the specified capacity.

For EVERY bus in the set, list:

• (i) the bus plate number,
• (ii) the model, and
• (iii) the total number of unique drivers who were born between the months of July to December and meanwhile have driven the bus (if there is no such driver, 0 is expected).

Answer 1

Use IFNULL(value, 0) to show 0 for no drivers found. Also you may like to enhance your query by using JOINS

SELECT b.PlateNumber, BusModel, IFNULL(Count(DISTINCT d.StaffID), 0) AS NumberOfDirvers
FROM Bus b LEFT JOIN Trip t ON b.PlateNumber = t.PlateNumber
LEFT JOIN Driver d ON t.StaffID = d.StaffID
LEFT JOIN Staff s d.StaffID = s.StaffID
WHERE EXTRACT(MONTH FROM s.DateOfBirth) BETWEEN '07' AND '12'
GROUP BY b.PlateNumber, BusModel;