Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 241 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Brief summary and diagnosis (for ): the correlated-subquery in the original post is returning a per-item subtotal and therefore repeats that subtotal on each matching RDN1 row. That explains the 7 and 5 values seen. To produce a single grand total (12) for the set of ItemCodes matched by the MET_OBOM/MET_BOM1 filter, either compute an aggregate over the filtered rows (single-row result), or use a window function without a PARTITION so the same grand total appears on every row. Also note two likely issues in the posted SQL: an ORDER BY inside the IN subquery is invalid, and the sample RDN1 shows no DocEntry column, so the ORDER BY T0.DocEntry in the outer query will fail.

A compact option that keeps the row detail but adds the grand total (no PARTITION):

WITH AllowedItems AS (
  SELECT DISTINCT o.U_ItemCode
  FROM MET_OBOM o
  JOIN MET_BOM1 b ON o.DocEntry = b.DocEntry
  WHERE b.U_ItemCode = '30-05-618XD'
)
SELECT r.ItemCode,
       r.Quantity,
       SUM(r.Quantity) OVER () AS GrandTotal
FROM RDN1 r
JOIN AllowedItems a ON r.ItemCode = a.U_ItemCode
ORDER BY r.ItemCode;

If only the overall total is required (single-row result), use a straightforward aggregate:

SELECT SUM(r.Quantity) AS GrandTotal
FROM RDN1 r
JOIN MET_OBOM o ON r.ItemCode = o.U_ItemCode
JOIN MET_BOM1 b ON o.DocEntry = b.DocEntry
WHERE b.U_ItemCode = '30-05-618XD';

If a grouped list plus a final total row is desired, aggregate by item and add a rollup:

SELECT r.ItemCode, SUM(r.Quantity) AS ItemTotal
FROM RDN1 r
JOIN MET_OBOM o ON r.ItemCode = o.U_ItemCode
JOIN MET_BOM1 b ON o.DocEntry = b.DocEntry
WHERE b.U_ItemCode = '30-05-618XD'
GROUP BY r.ItemCode WITH ROLLUP;

Practical notes: as observed, window functions are a good fit here; use SUM(...) OVER() with no PARTITION for a grand total. As ’s example hinted, plain SELECT with SUM but no GROUP BY will error if multiple rows are returned. Guard against duplicate joins (use DISTINCT in the AllowedItems CTE or pre-aggregate MET_OBOM) so the grand total is not inflated.

Recommended Answers

All 3 Replies

Member Avatar for 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

Edited by adam_k because: n/a
commented: I did test it, and it worked perfectly. +8
commented: Brilliant way to solve this problem +10
Member Avatar for 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
Edited by Eagletalon because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.