比较两个不同表的和

I am trying to compare Sum of quantities between 2 tables on A.BUSINESS_UNIT_PO,A.PO_ID, A.LINE_NBR. The below query doesn't work for me. Can any one of you tell me the problem in this query.

我正在比较A.BUSINESS_UNIT_PO A上两个表的数量之和。PO_ID A.LINE_NBR。下面的查询对我不起作用。你们谁能告诉我这个查询中的问题吗?

SELECT A.BUSINESS_UNIT_PO,
       A.PO_ID, 
       A.LINE_NBR 
 FROM PS_BCT_DTL A
WHERE A.BCT_DET_STATUS=1
  AND SUM(A.QTY) = (SELECT SUM(B.QTY_SH_RECVD) 
                     FROM PS_RECV_LN_SHIP B
                    WHERE A.BUSINESS_UNIT_PO=B.BUSINESS_UNIT_PO
                      AND A.PO_ID= B.PO_ID
                      AND A.LINE_NBR= B.LINE_NBR
                     )
GROUP BY A.BUSINESS_UNIT_PO,A.PO_ID, A.LINE_NBR

2 个解决方案

#1

Try to use having as below:

试着使用如下所示:

SELECT A.BUSINESS_UNIT_PO,
       A.PO_ID, 
       A.LINE_NBR,
       SUM(A.QTY) 
FROM PS_BCT_DTL A
WHERE A.BCT_DET_STATUS=1    
GROUP BY A.BUSINESS_UNIT_PO,A.PO_ID, A.LINE_NBR
having SUM(A.QTY) =(SELECT SUM(B.QTY_SH_RECVD) 
                 FROM PS_RECV_LN_SHIP B
                 WHERE A.BUSINESS_UNIT_PO=B.BUSINESS_UNIT_PO
                 AND A.PO_ID= B.PO_ID
                 AND A.LINE_NBR= B.LINE_NBR)

Here is more informations about having.

这里有更多关于拥有的信息。

#2

select
  A.BUSINESS_UNIT_PO,A.PO_ID,A.LINE_NBR,
  sum(a.qty) sumA,
  sum(b.qty) sumB
from
  a inner join
  b on 
   A.BUSINESS_UNIT_PO=B.BUSINESS_UNIT_PO AND 
   A.PO_ID= B.PO_ID  AND 
   A.LINE_NBR= B.LINE_NBR
group by
 A.BUSINESS_UNIT_PO,A.PO_ID,A.LINE_NBR
having
  sum(a.qty) = sum(b.qty);