Join 3 tables > group by > COUNT > How to show 0?

count the number of SELLING CAR_ID 1000 sold at each BRANCH: if a BRANCH did not sell any 1000 CAR_ID, PRINT 0

SELCT: employee join branch left join sellings

I want to count the number of cars sold at each branch where CAR_ID is 1000
If there is no car sold at a branch, I need to print out 0.

select
    s.id as SELLING_ID,
    s.car_id as CAR_ID,
    e.id as EMPLOYEE_ID,
--     COUNT(s.id),
    b.id as BRANCH_ID,
    b.name as BRANCH_NAME
from
    employee e
    join branch b on b.id  = e.branch_id 
    left join sellings s on e.id = s.employee_id
where 
    CAR_ID = 1000 OR
    CAR_ID is null
-- group by BRANCH_ID 
;

result from above

JOIN

• Table A left join Table B on A.id = B.A_ID
  This means on A.ID = B.A_ID
• show every A rows
• show every B rows if exists, if does not extis, show null for columns on B
  like pic above

SELECT: employee join branch left join sellings GROUP BY BRANCH_ID and COUNT(SELLING_ID)

select
--     s.id as SELLING_ID,
--     s.car_id as CAR_ID,
--     e.id as EMPLOYEE_ID,
    COUNT(s.id) 'COUNT',
    b.id as BRANCH_ID,
    b.name as BRANCH_NAME
from
    employee e
    join branch b on b.id  = e.branch_id 
    left join sellings s on e.id = s.employee_id
where 
    CAR_ID = 1000 OR
    CAR_ID is null
group by BRANCH_ID
order by BRANCH_ID asc
;

keys for counting the number of cars sold at each branch with count 0 is to COUNT SELLING_ID (if nothing exists, null) and CAR_ID is null where statement

result from above