SUM of Quantity in a table whilst using IN in WHERE clause

[SimpleByDesign]

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

[adam_k]

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

[Eagletalon]

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

[adam_k]

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