SQL multiple Joins without duplicates

Question

Suppose I have a customer table, and orders table and a quotes table. Each customer can have none, one or several order and quote entries.

I can get the quantity of products ordered for each customer like this:

SELECT CustomerName, SUM(Orders.OrdQuantity) as TotalOrderQty 
from Customer 
join Orders on Customer.id=Orders.CustomerId 
group by CustomerName;

And I can do a similar query to get the quotes quantity like this:

SELECT CustomerName, SUM(Quotes.QuoteQuantity) as TotalQuoteQty 
from Customer 
join Quotes on Customer.id=Quotes.CustomerId 
group by CustomerName;

But how would I do both in one query to give a result set like this:

CustomerName TotalOrderQty TotalQuoteQty

when I try and do multiple inner joins I get both quantities counted twice where a customer has entries in both tables.

score 1 · Accepted Answer · answered Mar 28 '23 at 15:00

But how would I do both in one query?

You would pre-aggregate before joining - or, since you just want one aggregate column from each table, you can also use correlated subqueries:

select c.customerName,
    (select sum(o.OrdQuantity)   from Orders o where c.id = o.CustomerId) TotalOrderQty,
    (select sum(q.QuoteQuantity) from Quotes q where c.id = q.CustomerId) TotalQuoteQty
from Customers c

SQL multiple Joins without duplicates

1 Answers1