I want to concatenate the result of these these two select queries as four columns in one table

Question

select 
    SUM (cp.TotalAmount) as totalPaymentamount,
    lvl4.SubSubsidaryAccountName as account1
from 
    TBLCPVMaster cp,TBLLevel4 lvl4
where 
    cp.SubSubsidaryAccountId = lvl4.SubSubsidaryAccountCode  
group by 
    lvl4.SubSubsidaryAccountName

select 
    SUM (cr.TotalAmount) as totalReciveamount,
    lvl4_2.SubSubsidaryAccountName as account2
from 
    TBLCRVMaster cr, TBLLevel4 lvl4_2
where 
    cr.SubSubsidaryAccountId = lvl4_2.SubSubsidaryAccountCode 
group by 
    lvl4_2.SubSubsidaryAccountName 

The resultant table should have 4 columns...please help

Thanks in advance

Answer 1

You really should start using the join syntax from ANSI-92. It has been 25 years.

with Payments as
(
    select SUM (cp.TotalAmount) as totalPaymentamount
        ,   lvl4.SubSubsidaryAccountName as account1
    from TBLCPVMaster cp
    join TBLLevel4 lvl4 on cp.SubSubsidaryAccountId = lvl4.SubSubsidaryAccountCode 
    group by lvl4.SubSubsidaryAccountName
)
, Receipts as
(
    select SUM (cr.TotalAmount) as totalReciveamount
        , lvl4_2.SubSubsidaryAccountName as account2
    from TBLCRVMaster cr
    join TBLLevel4 lvl4_2 on cr.SubSubsidaryAccountId = lvl4_2.SubSubsidaryAccountCode 
    group by lvl4_2.SubSubsidaryAccountName 
)

select p.totalPaymentAmount
    , p.account1
    , r.totalReciveAmount
    , r.account2
from Payments p
cross join Receipts r