SQL OUTER JOIN column sum returning twice the amount

I have a display query that essentially shows stock on hand, on order, and on reserve. It's all fine aside from one part - the Sum of weight and quantity is returning doubled results (only in the case of some amount being on order). I'm sure I've stuffed my joins up, but I can't figure out how. I'd be grateful for some insight! I'll answer any replies speedily.

Current output (with doubled sum):

    PartNo                     Branch SOHWeight SOHQty ReservedQty SOOEa SOOTo AvgCost type product grade coating finish thickness width length dim1 dim2 Notes                                                                                                                                                                                                                                                            ClassFBR ClassFME ClassFSY AltoQty
    -------------------------- ------ --------- ------ ----------- ----- ----- ------- ---- ------- ----- ------- ------ --------- ----- ------ ---- ---- -----
    B-254-304---5--40-40-6000  FME    0.33      18     NULL        NULL  1.5   68.7494 B    ANGLE-E 304           NULL   5         NULL  6000   40   40   NULL                                                                                                                                                                                                                                                             C        A        B        NULL
    Warning: Null value is eliminated by an aggregate or other SET operation.

Open in new window


Expected output is exactly the same, excepting that SOHWeight and SOHQty should be halved (compared to current output)

Notes:
 - StockOnHand will have many PackNoID with the same ProductDesc (Part#)
 - There can be multiple reservations against the same PackNoID


    SELECT dbo.tblProducts.PartNo,
           'FME'                                                                                                                                                                                                AS Branch,
           SUM(sOh.Weight)                                                                                                                                                                                      AS SOHWeight,
           SUM(sOh.Quantity)                                                                                                                                                                                    AS SOHQty,
           SUM(dbo.tblReserveDetail.Quantity)                                                                                                                                                                   AS ReservedQty,
           SUM(iif(dbo.tblPurchaseOrderDetail.QuantityUnit = 'TO', NULL, dbo.tblPurchaseOrderDetail.QuantityAmount))                                                                                            AS SOOEa,
           SUM(iif(dbo.tblPurchaseOrderDetail.QuantityUnit = 'TO', dbo.tblPurchaseOrderDetail.QuantityAmount, NULL))                                                                                            AS SOOTo,
           SUM(IIF(SoH.TYPE IN ( 'C', 'SC' ), SoH.COST * SoH.WEIGHT, SoH.COST * SoH.QUANTITY) + PROCESSINGCOST + PROCESSINGFREIGHT + Packaging) / SUM(IIF(SoH.TYPE IN ( 'C', 'SC' ), sOh.weight, sOh.Quantity)) AS AvgCost,
           tblProducts.type,
           tblProducts.product,
           tblProducts.grade,
           tblProducts.coating,
           tblProducts.finish,
           tblProducts.thickness,
           tblProducts.width,
           tblProducts.length,
           tblProducts.dim1,
           tblProducts.dim2,
           tblProducts.Notes,
           tblProducts.ClassFBR,
           tblProducts.ClassFME,
           tblProducts.ClassFSY,
           SUM(IIF(SoH.STATUS = 'SC', SoH.QUANTITY, NULL))                                                                                                                                                      AS AltoQty
    FROM   DBO.tblProducts
           LEFT OUTER JOIN dbo.tblStockOnHand SoH
             ON tblProducts.PartNo = SOH.ProductDesc
                AND SoH.Status IN ( 'I', 'R', 'SC' )
                AND SoH.branch = 'FME'
           LEFT OUTER JOIN dbo.tblReserveDetail
                           RIGHT OUTER JOIN dbo.tblReserveHeader
                             ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID
                                AND tblReserveHeader.Completed = 0
                                AND tblReserveHeader.Cancelled = 0
             ON SOH.PACKNOID = tblReserveDetail.PacknoID
           LEFT OUTER JOIN tblPurchaseOrderDetail
             ON dbo.tblPurchaseOrderDetail.ProductDesc = dbo.tblProducts.PartNo
                AND ( dbo.tblPurchaseOrderDetail.Status = N'O'
                       OR dbo.tblPurchaseOrderDetail.Status = N'PD' )
                AND tblPurchaseOrderDetail.branch = 'FME'
    GROUP  BY dbo.tblProducts.PartNo,
              tblProducts.type,
              tblProducts.product,
              tblProducts.grade,
              tblProducts.coating,
              tblProducts.finish,
              tblProducts.thickness,
              tblProducts.width,
              tblProducts.length,
              tblProducts.dim1,
              tblProducts.dim2,
              tblProducts.Notes,
              tblProducts.ClassFBR,
              tblProducts.ClassFME,
              tblProducts.ClassFSY 

Open in new window


Sample data (stripped down):
tblStockOnHand:

    PackNoID	Status	Weight	Quantity	Cost	ProcessingCost	ProcessingFreight	Packaging	Branch	tblstockonhand.ProductDesc
    157220	I	0.165	9	$68.47	$0.00	$2.48	$0.00	FME	B-254-304---5--40-40-6000
    158620	I	0.01839825	1	$64.27	$0.00	$4.72	$0.00	FSY	B-254-304---5--40-40-6000

Open in new window


tblPurchaseOrderDetail

    RecordId	Status	OrderNumber	ItemNumber	QuantityAmount	QuantityUnit	PriceQuantity	PriceUnit	Branch	ProductDesc
    6976	FD	4441	18	0.25	TO	3790	TO	FBR	B-254-304---5--40-40-6000
    6936	O	4439	23	0.5	TO	3790	TO	FME	B-254-304---5--40-40-6000
    6912	FD	4437	1	20	EA	75.96	EA	FSY	B-254-304---5--40-40-6000

Open in new window


tblProducts

    PartNo	ClassFBR	ClassFME	ClassFSY	Notes	Type	Product	Grade	Finish	Coating	Thickness	Width	Length	Dim1	Dim2
    B-254-304---5--40-40-6000	C	A	B		B	ANGLE-E	304			5		6000	40	40

Open in new window

LVL 3
jcrozier21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
It's a bit tricky to sort out without the sample data of all your tables, but my gut feel says the issue is with the reservation tables (the ones for which no sample data is posted).
You state the following:

- There can be multiple reservations against the same PackNoID

That means that this join:

LEFT OUTER JOIN dbo.tblReserveDetail
                           RIGHT OUTER JOIN dbo.tblReserveHeader
                             ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID
                                AND tblReserveHeader.Completed = 0
                                AND tblReserveHeader.Cancelled = 0
             ON SOH.PACKNOID = tblReserveDetail.PacknoID

Open in new window


Can return multiple records because it links your PackNoID from your stockOnHand to the reservations. If you happen to have 2 reservations for the same item, your stock on hand will double.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jcrozier21Author Commented:
Finally nailed it, as far as I can tell, I have to subquery the joins before OUTER joining them, otherwise instead of grouping/summing, they will sum for each record returned. Horrible explanation I know, but here is the end result query. Thanks to anyone who had a look.

   
SELECT  dbo.tblProducts.PartNo, 'FME' AS Branch, SUM(sOh.Weight) AS SOHWeight, SUM(sOh.Quantity) AS SOHQty, 
    		ReservedQty, SOOEa,SOOTo
    		,sum(IIF(SoH.TYPE IN ('C','SC'),SoH.COST*SoH.WEIGHT,SoH.COST*SoH.QUANTITY)+soh.PROCESSINGCOST+soh.PROCESSINGFREIGHT+soh.Packaging)/SUM(IIF(SoH.TYPE IN ('C','SC'),sOh.weight,sOh.Quantity)) AS AvgCost
    		,tblProducts.type,tblProducts.product,tblProducts.grade,tblProducts.coating,tblProducts.finish,tblProducts.thickness,tblProducts.width,tblProducts.length,tblProducts.dim1,tblProducts.dim2,tblProducts.Notes,tblProducts.ClassFBR,tblProducts.ClassFME,tblProducts.ClassFSY
    		,SUM(IIF(SoH.STATUS='SC',SoH.QUANTITY,NULL)) AS AltoQty
    FROM 
    		dbo.tblProducts 
    		LEFT OUTER JOIN
    		dbo.tblStockOnHand SoH  
    		ON tblProducts.PartNo = SOH.ProductDesc AND SoH.Status IN ('I', 'R','SC') AND SoH.branch='FME' 
    		LEFT OUTER JOIN
    			(SELECT SUM(tblReserveDetail.Quantity) AS ReservedQty, ProductDesc
    			FROM dbo.tblReserveDetail INNER JOIN tblReserveHeader ON dbo.tblReserveHeader.ID = dbo.tblReserveDetail.ReserveID INNER JOIN tblStockOnHand ON tblReserveDetail.PacknoID=tblStockOnHand.PackNoID
    			WHERE tblReserveHeader.Completed=0 AND tblReserveHeader.Cancelled =0 AND BRANCH='FME'
    			GROUP BY ProductDesc) RES
    		ON SoH.ProductDesc = RES.ProductDesc 
    		LEFT OUTER JOIN 
    			(SELECT tblPurchaseOrderDetail.ProductDesc,SUM(iif(QuantityUnit='TO',null,QuantityAmount)) AS SOOEa,SUM(iif(QuantityUnit='TO',QuantityAmount,null)) AS SOOTo 
    			FROM tblPurchaseOrderDetail WHERE
    			(dbo.tblPurchaseOrderDetail.Status = N'O' OR dbo.tblPurchaseOrderDetail.Status = N'PD') AND tblPurchaseOrderDetail.branch='FME'
    			GROUP BY tblPurchaseOrderDetail.ProductDesc) PoD
    		ON PoD.ProductDesc = dbo.tblProducts.PartNo
    GROUP BY dbo.tblProducts.PartNo,tblProducts.type,tblProducts.product,tblProducts.grade,tblProducts.coating,tblProducts.finish,tblProducts.thickness,tblProducts.width,tblProducts.length,tblProducts.dim1,tblProducts.dim2,tblProducts.Notes,tblProducts.ClassFBR,tblProducts.ClassFME,tblProducts.ClassFSY,SOOEa,SOOTo,ReservedQty

Open in new window

0
jcrozier21Author Commented:
Koen's answer was correct and helpful, but didn't suggest a solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.