Compare sum from 2 different tables
11,382
Solution 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.
Solution 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);
Related videos on Youtube
Author by
Narry
Updated on September 15, 2022Comments
-
Narry over 1 year
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.
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