×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Joining multiple tables - sum

Joining multiple tables - sum

Joining multiple tables - sum

(OP)
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

RE: Joining multiple tables - sum

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

RE: Joining multiple tables - sum

(OP)
Thanks Ian,

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

Luke.

RE: Joining multiple tables - sum

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close