MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table brands(brand_id int, name text);
insert into brands values
(1, 'Apple'), 
(2, 'Sony'),
(3, 'Nokia'),
(4, 'HTC');
create table products(id int, brand_id int, qty int);
insert into products values
(1, 1, 3),
(2, 3, 0),
(4, 1, 1),
(2, 2, 1);
select b.brand_id, 
       b.name,
       (select count(*)
        from   products 
        where  brand_id = b.brand_id
        and    qty > 0) as cnt
from   brands b;


brand_id
name
cnt


1
Apple
2

2
Sony
1

3
Nokia
0

4
HTC
0


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