How to Join this 2 tables ?
-
I have this 2 table with "GROUP BY" clause,
how can I join this 2 table togethers became
like this :
TransANo Prodcode sum(QtyCheck) sum(QtyOnHand) Different
-------- -------- ------------- -------------- -------------
001 A 12 66
001 B 9 27
002 C 514
Note : Different = sum(QtyCheck) - sum(QtyOnHand)
From :
Table 1 :
=========
Select TransANo,Prodcode,sum(QtyCheck)
From Trans_A
Where Sum(QtyCheck) > 0
group by TransANo,Prodcode
Result :
TransANo Prodcode sum(QtyCheck)
-------- -------- -------------
001 A 12
001 B 9
002 C 5
Table 2:
========
Select TransBNo,Prodcode,Sum(QtyOnHand)
From Trans_B
group by TransBNo,ProdCode
Result :
TransBNo Prodcode sum(QtyOnHand)
-------- -------- -------------
001 A 6
001 B 2
002 C 1
Thx,
Jonny
-
here it is one way you can do this..according to your problem..
SQL should be like this:-
SELECT t1.transano, t1.prodcode, SUM(t1.qty) AS transA_qtycheck, SUM(t2.qty) AS transB_qty_in_hand, SUM(t1.qty) - SUM(t2.qty) AS Diff
FROM
transA t1, transb t2
where
t1.transAno = t2.transBno AND
t1.prodcode = t2.prodcode
GROUP BY t1.transano, t1.prodcode
I hope this will be helpful..
SqlIndia
-
Here is another option for you:
select a.TransAno, a.Prodcode,a.SumQtyCheck, b.sum_qtyonhand, a.SumQtyCheck - b.sum_qtyonhand as different from
(Select TransANo,Prodcode,sum(QtyCheck) as Sum_QtyCheck
From Trans_A
Where Sum(QtyCheck) > 0
group by TransANo,Prodcode) A join
(Select TransBNo,Prodcode,Sum(QtyOnHand)as sum_qtyonhand
From Trans_B
group by TransBNo,ProdCode) b
on a.transAno=b.transBno
and a.prodcode=b.prodcode
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Thx u everyone who responded, It's really helpfull to solve my problem!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply