MySQL – category with product count not showing all results

I am trying to display the count of products along with the category name in the category list.

I was able to achieve this with JOIN, but not all categories are displayed.

I mean the categories with 0 products were not displayed, but I changed the JOIN to LEFT JOIN which displayed only one category with 0 products. There are two more.

SELECT c.id 
     , c.name 
     , count( p.category_id ) product_count 
  FROM category c 
  LEFT 
  JOIN products p 
    ON c.id = p.category_id 
 GROUP 
    BY p.category_id

This is the query I am trying to execute.

If anyone has any idea, please guide me.

Answer

Your query should fail because the GROUP BY clause is inconsistent with the SELECT columns — c.id is not the same as p.category_id. (Older version of MySQL do accept this syntax.)

You are aggregating by a column that has a NULL value when there is no match. You really want to aggregate by a column in the category table. I would recommend:

SELECT c.id, c.name , count( p.category_id ) AS product_count
FROM category c LEFT JOIN 
     products p
     ON c.id = p.category_id
GROUP BY c.id, c.name;

Note that the GROUP BY columns match the unaggregated SELECT columns. Also, the as id and as name are redundant, so I removed them.