Ok I have an SQL statement that is driving me crazy.
I have an item table (tblitem), a sales table (tblSales) and a branch transfer table (tblBranchTransfer) what I'm trying to do is return a list of all the items with the quantity the total quantity sold and the total quantity requested from other branches for each item. My code is as follows:
select i.itemid, Sum(s.Sales) As "Quantity Sold", Sum(t.Quantity)As "Quantity Requested"
from tblitem iLeft Join tblSales s
On i.itemid = s.ItemIdLeft Join tblBranchTransfer t
On i.itemid = t.ItemId
and TransferFlag = 'F'
group by i.itemid
The Quantity Sold appears fine however the Quantity Requested returns the wrong values. If for example item 1 appears twice in the tblSales and the total Quantity Requested is 10 then it will multiply that by 2 (the number of rows in tblSales). Does anyone know how to stop this from happening?
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).