Left Outer Join Problem using Sum

sql server Bulgaria
  • 12 years ago

    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 i

    Left Join tblSales s

    On i.itemid = s.ItemId

    Left 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?

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“XML is like violence - if it's not working for you, you're not using enough of it.”