Using GROUP BY but cannot get zero totals to show

[joesoaper]

I need to do a query that will show me all models and totals even if it is zero. The query only gives the models with 1 or more in stock. Sample here below.

 

Model 1                      5

Model 2                     15

Model 17                   10

 

This is the code I am using

 

                       $sql = 'SELECT status, model, COUNT(model) FROM club_inventory WHERE status="In-Stock" AND prod="Jets"  GROUP BY model ';
                       foreach ($pdo->query($sql) as $row) {
                                echo '<tr>';
                                echo '<td>'. $row['model'] . '</td>';
                                echo '<td>'. $row['COUNT(model)'] . '</td>';
                                echo '</td>';
                                echo '</tr>';
                       }

Any help would be greatly appreciated.

 

[Barand]

Your code is like walking into a room and asking "Will anyone who isn't here please raise their hand".

[joesoaper]

I have no idea what that means, if you cannot give any help just say so, if my code it totally wrong say so as well, then at least I will have an idea of where I am wrong and I can try and fix it..

[Barand]

How does a teacher check which pupils are absent from a class? S/he has a register of the pupils that should be there.

 

You are trying to count things that aren't there, so you need a "model" table containing a row for each model - your register.

 

You then query

SELECT i.status
 , m.model
 , COUNT(i.model)
FROM model m
LEFT JOIN club_inventory i
    ON m.model = i.model
    AND status="In-Stock"
    AND prod="Jets"  
GROUP BY m.model

Edited July 10, 2016 by Barand

[joesoaper]

Thanks for that, I really appreciate the help.