Make Mysql count even if value is equal to zero-CodePudding

I'm trying to count number of items by categories and i want to show 0 on categories with no items My actual code doen't consider categories with 0 value

SELECT categories.categories_id, categories_name, categories_active, categories_status, 
COUNT(IFNULL( product.product_id,0)) as count 
FROM categories 
LEFT JOIN product ON product.categories_id = categories.categories_id
WHERE categories_status = 1 AND product.active = 1
GROUP BY categories.categories_id

suppose we have two tables categories and products

cat_id  cat_name
1   cat A
2   cat B
3   cat C

product_id  product_name    cat_id
1               prod A      1
2               prod B      2
3               prod C      1
4               prod X      2

i expect to get

cat_id  cat_name    count products
1   cat A           2
2   cat B           2
3   cat C           0

CodePudding user response：

The problem is that you use

AND product.active = 1

and you filter out null values (categories without products) because of this condition. Try like this:

AND (product.active = 1 OR product.active IS NULL)

final query:

SELECT categories.categories_id, categories_name, categories_active, categories_status, 
COUNT(product.product_id) AS count 
FROM categories 
LEFT JOIN product ON product.categories_id = categories.categories_id
WHERE categories_status = 1 AND (product.active = 1 OR product.active IS NULL)
GROUP BY categories.categories_id

CodePudding user response：

Try using COUNT(*). It counts every row generated by your from/ join / where clauses.

CodePudding user response：

Instead of left join use right join and change the COUNT(IFNULL( product.product_id,0)) to COUNT(product.product_id).Try this once.

SELECT categories.categories_id, categories_name, categories_active, categories_status, 
    COUNT(product.product_id) as count 
    FROM categories 
    RIGHT JOIN product ON product.categories_id = categories.categories_id
    WHERE categories_status = 1 AND product.active = 1
    GROUP BY categories.categories_id

By right join you will get count 0 data also