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).

Consider providing some sample data and the desired result - ideally as DDLs and/or an Sqlfiddle — Strawberry
– Strawberry, Commented Mar 20, 2013 at 10:31

Muhammad Hani · Accepted Answer · 2013-03-20 10:56:02Z

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;

edited Mar 20, 2013 at 10:56

answered Mar 20, 2013 at 10:34

Muhammad Hani

8,6725 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

skaaby Over a year ago

where do i write in ifnull(value,0)?

Gordon Linoff Over a year ago

@MuhammadHani . . . The ifnull is totally unnecessary. COUNT(DISTINCT) will return 0 if there are no matches.

skaaby Over a year ago

the problem here lies with LEFT JOIN Staff s d.StaffID = s.StaffID as there are some bus who doesn't have any drivers... but how to get it set as 0 in the column number of drivers?

skaaby Over a year ago

@MuhammadHani the problem here lies with LEFT JOIN Staff s d.StaffID = s.StaffID as there are some bus who doesn't have any drivers... but how to get it set as 0 in the column number of drivers?

Collectives™ on Stack Overflow

SQL sub query counting numbers

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related