asked
How to aggregate among the values of different tables in a single query?
[Tables Diagram][1] These are my datas from 3 tables. select*from ShippingDetails ProductCode Shipping Quantity MFD01-10 50 MFD01-07 50 MFD01-10 10 select*from ProductDetails ProductCode Shipping Quantity MFD01-07 500 MFD01-10 100 MFD01-07 1000 MSD01-21 200 select*from StockData ProductCode UrunAdi ( " Product Name") MFD01-07 7 mm FTube MFD01-10 10 mm FTube MSD01-21 21 mm STube MSD01-27 27 mm STube I try to write these two queries but it didnt work. I couldn't merge as one table. select StockData.ProductCode,SUM( ProductDetails.ProductQuantity) as ' Product Quantity' from ProductDetails RIGHT OUTER JOIN StockData on ProductDetails.ProductCode=StockData.ProductCode group by StockData.ProductCode Product Code Product Quantity MFD01-07 1500 MFD01-10 100 MSD01-21 200 MSD01-27 NULL select StockData.ProductCode, SUM ( ShippingDetails.ShippingQuantity) as ' Shipping Quantity' from ShippingDetails RIGHT OUTER JOIN StockData on ShippingDetails.ProductCode=StockData.ProductCode group by StockData.ProductCode Product Code Shipping Quantity MFD01-07 50 MFD01-10 60 MSD01-21 NULL MSD01-27 NULL **This result that i need.** Which query would give it? I will appreciate if you solve my issue. Product Code (Product-Shipping) Quantity MFD01-07 1450 MFD01-10 40 MSD01-21 200 MSD01-27 NULL [1]:
http://i.stack.imgur.com/bguUD.png
You could combine the two sets into one with a UNION and then select from that new set: select ProductCode , sum([Shipping Quantity]) from ( select ProductCode , [Shipping Quantity] from ShippingDetails union all select ProductCode , [Shipping Quantity] from ProductDetails ) x group by ProductCode
18 People are following this question.
