CODEWITHSUNDEEP
sqlcount
BARNZ
BARNZ

Reputation: 1329

SQL Help - Counting rows from two tables

I have 2 tables: products & category

I'm trying to write the sql to count the number of products per category. So far I have this:

SELECT `name`, count(*) as `size`
FROM products
INNER JOIN category ON category.`id` = products.`categoryID`
GROUP BY category.`name`

This will return me a list of each category name and the number of products for that category. However it doesn't list categories that have 0 products. How do I solve this?

Upvotes: 1

Views: 737

Answers (2)

Isaac Fife
Isaac Fife

Reputation: 1690

Use a right join. 

SELECT `name`, count(*) as `size`
FROM products
RIGHT JOIN category ON category.`id` = products.`categoryID`
GROUP BY category.`name`

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26871

Try with:

SELECT `name`, count(products.id) as `size`
FROM category
LEFT JOIN products ON category.`id` = products.`categoryID`
GROUP BY category.`name`

Upvotes: 0

Related Questions