I need a query to find an totals for items and writeins for each company. I have an Order table, an Orderdetails table, and a OrderWritein table. Both OrderDetails and OrderWriteIns have a Price and Quantiy column. I wrote this with joins as follows:
Select C.CompanyName,sum(D.price*D.Quantity) itmAmt,sum (W.price*W.Quantity) wiAmt,max(O.OrderDate) [Last Order]
From tblOrders O
Inner Join tblCompany C on O.companyid = C.companyid
Inner Join tblOrdersDetail D on O.orderid=D.orderid
Inner Join tblOrdersWriteIns W on O.orderid=W.orderid
group by C.companyName
The problem is when I join the write in table it does a cross join. if I have 10 items and 10 writeIns then the sum is for the 100 rows. How do I write the query so each company has the correct sum for both itemsDetail and writeIns?