Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products
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 &gt; 0
GROUP BY b.brand_id