Hi All

I need to use this query as a subquery

select sum("A"."QUANTITY" * "B"."Qty") as Qty
from A  Inner Join B on "B"."ITEMNO" = "A"."ITEMNO" and "A"."BOMNO"="B"."BOMNO" and "A"."STATUS" = 1 group by "B"."Component"

with the complete query returning

"C"."ITEMNO"   Qty     

with "C"."ITEMNO" = "B"."Component"

Appreciate any help with this. Thanks in advance

What is your SQL Server version (assuming you're using SQL Server) and can you post the whole query?

I suggest to use CROSS APPLY in order to bring this information into the query, but that's all I can say based on the very limited information provided.

PluralSight Learning Library

Markros, SQL 2008 server.

The whole query is selecting a Item from master item table, and adding subquery to return qty on sales order, PO and committed to production.

In the above instance

Table c
Itemno

Table a
Itemno (master item)
Bomn
Qty

Table b
Itemno (master item)
Bomno
Component (same as c.itemno)
Qty

Component Committed to production = a.qty*b.qty

Query to return ( the so and PO subquery look at one table and so straight forward, but the committed to prod is calc from 2 tables)

C.item.         Committed to production.     On so.       On PO
A.                      0.                                         20.             0
B.                    100.                                        0.              10

Check this blog post
Aggregates with multiple tables
 

PluralSight Learning Library

It looks like you want the Sum of (A.Qty * B.Qty), not the sum of A.Qty multiplied by the sum of B.Qty.  I would use this SQL with a derived table based on your subquery above:


You may want to change the join types depending on what you want to see if there are no records in B or A for your C.ItemNo values.
 

Just what I needed.Many Thanks Jonfer.