COUNT is grouping rows unexpectedly

Question

Table items keeps track of the different types of items that can be stocked.

item_type  item_name  last_stock

   1         cake      2010-08-10
   2         fruit     2010-08-07
   3         soda      2010-08-07
   4         water     2010-08-09

Table individual_items keeps track of each specific item.

   id   item_type

    1          1 
    2          2        
    3          1           

My query in MySQL:

SELECT i.item_type, i.item_name, COUNT(j.id)
FROM items i
LEFT OUTER JOIN individual_items j
ON i.item_type = j.item_type
GROUP BY j.item_type

However, the COUNT(j.id) is screwing with my result. It appears to be grouping any items that are defined but not actually in existence.

item_type    item_name  COUNT(j.id)
    1           cake         2
    2           fruit        1
    3           soda         0   

I think the expected fourth row 4 water 0 is not appearing because COUNT() is incorrectly grouping the non-existent rows that result from the LEFT OUTER JOIN. How can I fix this?

Answer 1

Try using a RIGHT JOIN.

Answer 2

You should group by all fields in your SELECT statement except those in aggregative functions

SELECT i.item_type, i.item_name, COUNT(j.id)
FROM items i
LEFT OUTER JOIN individual_items j
ON i.item_type = j.item_type
GROUP BY i.item_type, i.item_name

update: I've tested on my local mysql server, the code above should work

Answer 3

Your GROUP BY clause is probably the culprit here. You're grouping by the table that may not contain an item type, so in the case of item type 3 and 4, j.item_type is NULL (while i.item_type would contain the expected values). The problem should fix itself if you group by the item_type on i instead.

Answer 4

Try

SELECT i.item_type, i.item_name, COUNT(j.id)
FROM items i
LEFT OUTER JOIN individual_items j
ON i.item_type = j.item_type
GROUP BY i.item_type,i.item_name