Joining multiple tables - sum

[lukefuller01]

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.
[tt]

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

[/tt]
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;

[tt]

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

[/tt]

 

[IanWaterman]

Try replacing the table Sys2OrderItems with a select statement which returns the data you need for each order.

Then in you main select do a maximum of that result rather than a sum.

Ian

 

[lukefuller01]

Thanks Ian,

A little lost there ... do you mind showing an example ?

Luke.

 

[IanWaterman]

Sorry but I can't see how your list

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

Relates to headers in your full query.

Basically what I am suggesting is if you have atable which is causing rows to duplicate then replace it with a select statement which summarises the data you actually want to see.

Then join that in in much the same way you dod a table

Left Outer Join(Select ... from... group by ....)
On -- relevant fields

Ian