CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

How to find total number of products under each category using mysql joins?

I have 2 tables - catgories and products. I want to get total number of products falling under each category? My tables look as:

table categories

category_id     category_name
1               first category
2               second category
3               third category

table products

product_id     product_name     category_id
1              first product    1
2              second product   1
3              third product    1
4              fourth product   3
5              fifth product    3

I want following output:

category_id     category_name     total_products
1               first category    3
2               second category   0
3               third category    2

I am currently using following sql but it is not yielding me correct result:

SELECT `c`.`category_id`, `c`.`category_name`, COUNT(`p`.`product_id`) AS total_products FROM `categories` AS `c` INNER JOIN `products` AS `p` ON `c`.`category_id` = `p`.`product_id` GROUP BY `p`.`category_id`

Upvotes: 2

Views: 2562

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to use left join as

select
c.*, coalesce(count(p.category_id),0) as total_products
from categories c
left join products p on p.category_id = c.category_id
group by c.category_id ;

Upvotes: 1

Related Questions