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).

Contribute

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.”