troubleshooting Question

SQL Problem

Avatar of Richard Cooper
Richard CooperFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
4 Comments1 Solution317 ViewsLast Modified:
I have been asked to look at the following code as it is tripling the results:
SELECT     TOP 100 PERCENT InvMaster.ProductClass, InvMaster.StockCode, InvMaster.Description, InvMovements.TrnMonth, InvMovements.TrnType, 
                      InvMovements.TrnYear, SUM(DISTINCT InvWarehouse.QtyOnHand) AS SumQTY, InvMovements.MovementType, SUM(InvMovements.TrnQty) 
                      AS TranQty, SUM(InvWarehouse.UnitCost) AS Unitcost
FROM         InvMovements INNER JOIN
                      InvMaster ON InvMovements.StockCode = InvMaster.StockCode INNER JOIN
                      InvWarehouse ON InvMovements.StockCode = InvWarehouse.StockCode
GROUP BY InvMaster.ProductClass, InvMaster.StockCode, InvMaster.Description, InvMovements.TrnYear, InvMovements.TrnMonth, InvMovements.TrnType, 
                      InvMovements.MovementType
HAVING      (InvMovements.TrnYear = 2013) AND (InvMaster.ProductClass = 'CH') AND (InvMovements.TrnMonth = 01)
ORDER BY InvMovements.TrnMonth, InvMaster.StockCode

If I break it down to:
SELECT     TOP 100 PERCENT InvMaster.ProductClass, InvMaster.StockCode, InvMaster.Description, InvMovements.TrnMonth, InvMovements.TrnType, 
                      InvMovements.TrnYear, SUM(InvMovements.TrnQty) AS TranQty, InvMovements.MovementType
FROM         InvMovements INNER JOIN
                      InvMaster ON InvMovements.StockCode = InvMaster.StockCode
GROUP BY InvMaster.ProductClass, InvMaster.StockCode, InvMaster.Description, InvMovements.TrnYear, InvMovements.TrnMonth, InvMovements.TrnType, 
                      InvMovements.MovementType
HAVING      (InvMovements.TrnYear = 2013) AND (InvMaster.ProductClass = 'CH') AND (InvMovements.TrnMonth = 01)
ORDER BY InvMovements.TrnMonth, InvMaster.StockCode

I get the correct values

How can I add the
SUM(DISTINCT InvWarehouse.QtyOnHand) AS SumQTY,
To show the Total qty on hand for the StockCode not add a row for each Warehouse as it does in the first example.

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros