0

I have table:

brands

brand_id  name
1         Apple
2         Sony
3         Nokia
4         HTC

products

id  brand_id qty
1   1        3
2   3        0
4   1        1
2   2        1

I need to get (all brands from brands table and products.qty > 0)

brand_id   count_products
1          2
2          1
3          0
4          0

I tried

SELECT `p`.`brand_id` AS `id`,
       COUNT(p.brand_id) AS COUNT
FROM `products` AS `p`
WHERE AND `p`.`qty` > 0
GROUP BY `p`.`brand_id`

But I get results only for brands that available in products_table. Thanks for help.

3
SELECT b.brand_id,
       COUNT(p.brand_id)
FROM brands b
LEFT JOIN products p ON b.brand_id = p.brand_id
                    AND p.qty > 0
GROUP BY b.brand_id
0

If you need only brands that exists in products table:

select distinct p.brand_id,
       (select count(*)
        from   products 
        where  brand_id = p.brand_id
        and    qty > 0) as cnt
from   products p;
brand_id | cnt
-------: | --:
       1 |   2
       3 |   0
       2 |   1

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.