CODEWITHSUNDEEP
mysqlsql
notGeek
notGeek

Reputation: 1528

SQL query to get number of products for each category

I have a database for an e-commerce store. I'm trying to know the number of active products for each category.

Code for that:

SELECT c.id_category, COUNT(cp.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product
WHERE p.active=1
GROUP BY c.id_category 
ORDER BY nproducts ASC

However, it is not showing categories with 0 products. What am I missing?

Upvotes: 1

Views: 11377

Answers (1)

Nick
Nick

Reputation: 147166

Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.

SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category 
ORDER BY nproducts ASC

Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.

Upvotes: 3

Related Questions