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
– zerkms, Commented Apr 25, 2013 at 9:27
Did you try a left join? join on its own is an inner join, which is a different type of join - see stackoverflow.com/questions/38549 . — user359040
– user359040, Commented Apr 25, 2013 at 9:31
possible duplicate of Difference between inner and outer join — user359040
– user359040, Commented Apr 25, 2013 at 9:32

xlecoustillier · Accepted Answer · 2013-04-25 09:31:07Z

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, 2013 at 9:31

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

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

1 Comment

Pooshonk Over a year ago

Thank you! I knew it would be something simple that I would overlook +1

Federico Razzoli · Accepted Answer · 2013-04-25 09:32:19Z

0

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;

answered Apr 25, 2013 at 9:32

Federico Razzoli

5,5411 gold badge22 silver badges22 bronze badges

Comments

Denis de Bernardy · Accepted Answer · 2013-04-25 09:36:39Z

0

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

answered Apr 25, 2013 at 9:36

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

COUNT ifnull mysql query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related