View previous topic :: View next topic
|
Author |
Message |
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Hi All,
I have two tables
Table 1
Code: |
Acc_Num Acct_cde Amount
1 A 10
1 B 11
3 A 10
3 B 11 |
Table 2:
Code: |
Acc_Num Acct_cde Amount
1 C 10
2 A 12
2 A 10
2 C 13
3 B 14 |
I need a report to get the Acc_num, Acc_code & sum of there amount:
Code: |
Acct_num Acct_cde_A_AMt Acct_cde_B_Amt Acct_cde_C_Amt
1 10 11 10
2 22 - 13
3 10 25 - |
Please advise me the sql for same |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Hi Ekta,
You can try the following query.
Code: |
Select C.Acct_num, C.Acct_cde, sum(C.Amount)
From
(Select A.Acct_num, A.Acct_cde, A.Amount
From T1 A
Union
Select B.Acct_num, B.Acct_cde, B.Amount
From T2 B) C
Group by C.Acct_num, C.Acct_cde |
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
You should use UNION ALL. Otherwise there could be a small problem when T1 and T2 have an identical row.
IF the account codes are known (and i suppose so, because you have 3 columns)
AND you need the SQL to deliver the report in that layout (otherwise Srihari's solution is better )
Code: |
Select
c.acct_num
,sum(case when c.acct_cde = 'A' then c.amount end)
,sum(case when c.acct_cde = 'B' then c.amount end)
,sum(case when c.acct_cde = 'C' then c.amount end)
from
(Select A.Acct_num, A.Acct_cde, A.Amount
From T1 A
Union all
Select B.Acct_num, B.Acct_cde, B.Amount
From T2 B ) C
Group by C.Acct_num |
|
|
Back to top |
|
|
|