Joining multiple tables & calculating SUM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining multiple tables & calculating SUM

Author

Topic

lukefuller
Starting Member

1 Post

Posted - 2010-07-28 : 07:54:28

Hi All,

I have been pulling my hair out over this one.

What we are trying to do is pull sum values for the same item out of three different tables in the same DB (MSSQL '05) – Should be quite straight forward however...

The result should look a bit like below;

item code | initial population value | ordered value | sold value | cost price

The below query is pulling everything correctly except ordered value.

I have a suspicion the below snippet of the statement is to blame for this – the value that return from this below snippet are inflated by many multiples.

LEFT OUTER JOIN
Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2
INNER JOIN
Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND
Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND
Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate

I believe what may be happening is if the Order (Sys2Order) has multiple items (Sys2OrderItems) it is calculating the SUM value by the amount of items in the order to due to the number of rows in Sys2OrderItems for that ordernumber.

I hope this makes sense however been on this all day and may be totally wrong.

Any help greatly appreciated.

Full query code below;

SELECT BaseListing.Code, InitialValues.value AS InitialPopulation, SUM(Sys2InvoiceItems.Quantity) AS Sold,
SUM(Sys2OrderItems.qty) AS recv, Sys1Item.Description, ItemGroupsParent.Name AS PrimaryGroup,
ItemGroupsChild.Name AS ChildGroup, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2,
SUM(Sys2OrderItems.qty) AS Expr1
FROM BaseListing
LEFT OUTER JOIN
InitialValues ON BaseListing.Code = InitialValues.code AND
InitialValues.location = @LocatioNumber
LEFT OUTER JOIN
Sys1Item ON Sys1Item.ItemNo = BaseListing.Code AND Sys1Item.ItemTypeID = 15 AND Sys1Item.Deleted = 0 LEFT OUTER JOIN
Sys2InvoiceItems ON Sys2InvoiceItems.ItemID = Sys1Item.ItemID AND Sys2InvoiceItems.EntryDate > 733953
LEFT OUTER JOIN
Sys2Invoices ON Sys2Invoices.InvoiceID = Sys2InvoiceItems.InvoiceID AND Sys2Invoices.EntryDate > 733953 LEFT OUTER JOIN
Sys2Accounts ON Sys2Accounts.RoomID = Sys2Invoices.RoomID AND Sys2Accounts.Deleted = 0 AND
Sys2Accounts.Inactive = 0
LEFT OUTER JOIN
Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2 INNER JOIN
Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND
Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND
Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate
LEFT OUTER JOIN
Sys2Item ON Sys2Item.Code = BaseListing.Code
LEFT OUTER JOIN
ItemGroupsParent ON ItemGroupsParent.ID = Sys2Item.Primary_ID
LEFT OUTER JOIN
ItemGroupsChild ON ItemGroupsChild.ID = Sys2Item.Sub_ID
WHERE (BaseListing.IsPhysical = 0)
GROUP BY BaseListing.Code, InitialValues.value, Sys1Item.Description, ItemGroupsParent.Name,
ItemGroupsChild.Name, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 09:42:09

If an order has multiple items and you don't want the sum() of the qty then don't sum() it.
What dou want instead? The Number of items in an order or ...
Maybe you can give some sample data and wanted output in relation to the sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources