COUNT ifnull mysql query

Question

I have 2 tables in my database, fleets and personnel_onboard_fleets. I am trying to get a list of all of the fleets (which works) and count how many personnel are onboard each fleet (which doesn't). However, it isn't showing up the results if there aren't any personnel onboard.

SELECT f.*, count(pof.ID) AS onboard 
FROM fleets f, personnel_onboard_fleets pof 
WHERE f.fleet_ID = pof.fleet_ID ORDER BY f.status

I am expecting 2 results, one fleet with 2 people on board and one with zero people onboard. However I only get the one result. I have tried to use the following

SELECT f.*,  IFNULL(COUNT(pof.ID), 0) AS onboard 
FROM fleets f, personnel_onboard_fleets pof 
WHERE f.fleet_ID = pof.fleet_ID ORDER BY f.status

I cant really see what is wrong with the query, is there anything else that needs to be added to show fleets with 0 persons onboard.

My original query before the count shows all fleets fine, so I know it is something to do with the count.

This is driving me crazy! Any help would be much appreciated!!

Google for `LEFT JOIN` and check the difference between `INNER JOIN` and `LEFT JOIN` — zerkms, Apr 25 '13 at 09:27
Did you try a **left** join? `join` on its own is an `inner join`, which is a different type of join - see http://stackoverflow.com/questions/38549 . — , Apr 25 '13 at 09:31
possible duplicate of [Difference between inner and outer join](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join) — , Apr 25 '13 at 09:32
A left join, it was showing the same result as the first query. I've got it now, thanks — Pooshonk, Apr 25 '13 at 09:33

score 2 · Accepted Answer · answered Apr 25 '13 at 09:31

2

Try:

SELECT f.fleet_id,
       Count(pof.id) AS onboard
FROM   fleets f
       LEFT JOIN personnel_onboard_fleets pof
              ON f.fleet_id = pof.fleet_id
GROUP  BY f.fleet_id
ORDER  BY f.status;

answered Apr 25 '13 at 09:31

xlecoustillier

16,183
14
60
85

Thank you! I knew it would be something simple that I would overlook +1 – Pooshonk Apr 25 '13 at 09:34

score 0 · Answer 2 · answered Apr 25 '13 at 09:32

COUNT() is an aggregate function, it returns the total number of rows. If you specify a field name (as you did), the number of non-NULL values is returned.

You need to use GROUP BY. This aggregates rows based on a field. And then, COUNT() returns the total for each group.

SELECT f.fleet_ID, count(pof.ID) AS onboard
    FROM fleets f LEFT JOIN personnel_onboard_fleets pof
    ON f.fleet_ID = pof.fleet_ID
    GROUP BY f.fleet_ID
    ORDER BY f.status;

score 0 · Answer 3 · answered Apr 25 '13 at 09:36

Your inner join will eliminate any row in pof that doesn't satisfy the = operator.

You need a left join, e.g.:

SELECT f.*, COUNT(pof.fleet_ID) AS onboard 
FROM fleets f
LEFT JOIN personnel_onboard_fleets pof ON f.fleet_ID = pof.fleet_ID
GROUP BY f.fleet_ID ORDER BY f.status

COUNT ifnull mysql query

3 Answers3