0

Hi,

I am trying to add up the quantity of various items found in a table, that is controlled by a second/third joined table using the IN search criteria. However I am unable to work out getting the SUM of all the items.

The tables are as below:

Table One: RDN1
ItemCode         Quantity
80-05-910            5
80-05-910            2
80-21-910            3
80-05-918            5

Table Two: MET_OBOM
DocEntry         ItemCode
1                 80-05-910
2                 80-05-918
3                 80-21-910

Table Three: MET_BOM1
DocEntry         ItemCode
1                 30-05-618XD
2                 30-05-618XD
3                 30-05-619XD

Query so far:

SELECT T0.ItemCode, T0.Quantity (SELECT SUM(T1.Quantity) FROM RDN1 T1 WHERE T0.ItemCode = T1.ItemCode ) 
FROM RDN1 T0 
WHERE T0.ItemCode IN (SELECT T0.U_ItemCode  FROM MET_OBOM  T0 INNER JOIN MET_BOM1  T1 ON T0.DocEntry = T1.DocEntry WHERE T1.U_ItemCode = '30-05-618XD') 
ORDER BY T0.DocEntry ASC

From the query I get:

ItemCode         Quantity     SUM
80-05-910            5         7
80-05-910            2         7
80-05-918            5         5

Is there any way to get SUM to include all ItemCodes return i.e. SUM = 12

Any help is much appreciated.

Kind Regards,
Matthew

3
Contributors
3
Replies
6
Views
5 Years
Discussion Span
Last Post by adam_k
2

I preffer to use joins, so here's a possible solution:

select T0.ItemCode, T0.Quantity, sum(T0.Quantity) OVER(PARTITION BY T2.U_ItemCode) as 'SUM'
FROM RDN1 T0 inner join MET_OBOM  T1 
on T0.ItemCode = T1.ItemCode 
INNER JOIN MET_BOM1 T2 ON T1.DocEntry = T2.DocEntry 
WHERE T2.U_ItemCode = '30-05-618XD'
ORDER BY T1.DocEntry ASC

I haven't tested it, so it might contain errors.
You can read more info on OVER here: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Edited by adam_k: n/a

Votes + Comments
Brilliant way to solve this problem
I did test it, and it worked perfectly.
0

Hey mate... change your Subquery (in the select statements)... take away the joing and everything...

SELECT T0.ItemCode, T0.Quantity, SUM(T0.Quantity)

FROM RDN1 T0 

WHERE T0.ItemCode IN (SELECT T0.U_ItemCode  FROM MET_OBOM  T0 INNER JOIN MET_BOM1  T1 ON T0.DocEntry = T1.DocEntry WHERE T1.U_ItemCode = '30-05-618XD') 

ORDER BY T0.DocEntry ASC

See how that treats you

If not... try this:

SELECT T0.ItemCode, T0.Quantity, (SELECT SUM(T1.Quantity) FROM RDN1 T1 

WHERE T1.ItemCode IN (SELECT T0.U_ItemCode  FROM MET_OBOM  T0 INNER JOIN MET_BOM1  T1 ON T0.DocEntry = T1.DocEntry WHERE T1.U_ItemCode = '30-05-618XD') 

ORDER BY T0.DocEntry ASC)

FROM RDN1 T0 

WHERE T0.ItemCode IN (SELECT T0.U_ItemCode  FROM MET_OBOM  T0 INNER JOIN MET_BOM1  T1 ON T0.DocEntry = T1.DocEntry WHERE T1.U_ItemCode = '30-05-618XD') 

ORDER BY T0.DocEntry ASC

Edited by Eagletalon: n/a

0

@eagleton: For some reason I'm pretty sure that your first query requires a group by, and by placing the group by you've missed the aggregation as the request was the sum of quantity per item, in the same line with the quantity per order.

For your second query, I've got the feeling that it will cause a cartesian join and that the sum will be the total quantity of all RND records for that MET_BOM1.ItemCode as you are not grouping by MET_OBOM.ItemCode.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.