Subquery help 1

[coolstrike23]

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

 

[markros]

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

 

[coolstrike23]

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

 

[markros]

select I.*, coalesce(A.TotalQty,0) * coalesce(B.TotalQty,0) as [Commited to Production]
from Items I
OUTER APPLY (select sum(Qty) as TotalQty from TableA where A.ItemNo = I.ItemNo) A
OUTER APPLY (select sum(Qty) as TotalQty from TableB where B.ItemNo = I.ItemNo) B

Check this blog post
Aggregates with multiple tables


PluralSight Learning Library

 

[JonFer]

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:

Select C.*, D.Qty
From C Left Join
(Select B.Component, 
    Sum(IsNull(A.Qty,0) * IsNull(B.Qty,0)) as Qty
 From B Inner Join A on B.ITEMNO = A.ITEMNO  And 
      B.BOMNO=A.BOMNO and A.STATUS = 1 
Group By B.Component) as D 
   On C.ItemNo = D.Component

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.

 

[coolstrike23]

Just what I needed.Many Thanks Jonfer.