sql select count of multiple relationships with left join

Question

I have a table for "branches", "orders" and "products. Each order and product are connected to a branch with branch_id. I need an sql statement to get a list of all branches with a field for how many orders and a field for how many products.

This works:

SELECT b.*, COUNT(o.id) AS orderCount FROM branches b LEFT JOIN orders o ON (o.branch_id = b.id) GROUP BY b.id

but it only gets the amount of orders, not products. If I change it to add amount of products, the amounts are wrong because it's getting amount of orders * amount of products.

How can I get the amount of both the orders and the products in the same SQL statement?

Answer 1

Something like this should work (on sql server at least - you didn't specify your engine).

SELECT 
    b.id
   ,COUNT(distinct o.id) AS orderCount 
   ,COUNT(distinct p.id) AS productCount 
FROM branches b 
LEFT JOIN orders o 
    ON (o.branch_id = b.id)
left join products p
    on p.product_id=b.id)
GROUP BY 
    b.id