erşan Kuneri erşan Kuneri - 2 years ago 61
SQL Question

How to aggregate among the values of different tables in a single query?

Tables Diagram

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

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

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

Answer Source

You need a subquery for each table and then join together to calculate the total.

SQL Fiddle Demo

SELECT SD.[ProductCode],
       COALESCE(PD.stock, 0) - COALESCE(D.sales, 0)  as [(Product-Shipping) Quantity]
FROM StockData SD    
LEFT JOIN (SELECT [ProductCode], SUM([Product Quantity]) stock
           FROM ProductDetails
           GROUP BY [ProductCode]) PD
       ON SD.[ProductCode] = PD.[ProductCode]
LEFT JOIN (SELECT [ProductCode], SUM([Shipping Quantity]) sales
           FROM ShippingDetails
           GROUP BY [ProductCode]) D
       ON SD.[ProductCode] = D.[ProductCode]


| ProductCode | (Product-Shipping) Quantity |
|    MFD01-07 |                        1450 |
|    MFD01-10 |                          40 |
|    MSD01-21 |                         200 |
|    MSD01-27 |                           0 |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download