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

What's your question? All I see are two SQL statements and an expected result. — Bob Kaufman, Mar 05 '15 at 20:06
these are two differnt quries and gives result as two differnt tables with two columns each table .. i want to join all the columns together of these two tables — sheikh muzammil, Mar 05 '15 at 20:09
please add that to your question rather than just placing it here in the comments where it may be missed. — Bob Kaufman, Mar 05 '15 at 20:10
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Mar 05 '15 at 20:46

Sean Lange · Accepted Answer · 2015-03-05T20:26:43.017

2

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

edited Mar 05 '15 at 20:26

answered Mar 05 '15 at 20:09

Sean Lange

33,028
3
25
40

thank you for your answer .. but it says invalid column names – sheikh muzammil Mar 05 '15 at 20:14
You have to give the subquery an alias for it to work. I edited my response above. – Sean Lange Mar 05 '15 at 20:15
still not working :( totalreciveamount and account 2 invalid column names :( – sheikh muzammil Mar 05 '15 at 20:21
Oh good grief. The original code I posted isn't going to do what you want. You need to get the two resultsets differently. Here is one way to do it. – Sean Lange Mar 05 '15 at 20:26
actually i am beginner in writing sql quries and these joins really sucks :( and still its not working :D – sheikh muzammil Mar 05 '15 at 20:33
you answer gives me the desired result but with repeating rows .... i.e. the CPVMASTER table contains 6 rows but it returns me 18 rows ..it doesn't group up the account names!! – sheikh muzammil Mar 05 '15 at 20:48
Well...I have nothing to test this with. I am coding in the dark here. Please provide the ddl and sample data. sqlfiddle.com would be a good place to start. – Sean Lange Mar 05 '15 at 20:52

G B · Answer 2 · 2015-03-05T20:50:02.777

0

Im just taking the simplest understanding of what I think you are trying to achieve... get the total paid and total received for each account.

select 
    lvl4.SubSubsidaryAccountName, -- surely you only need name once?
    SUM (cp.TotalAmount) as totalPaymentamount,
    SUM (cr.TotalAmount) as totalReciveamount
from 
    TBLLevel4 lvl4
    left join TBLCPVMaster cp 
        on cp.SubSubsidaryAccountId=lvl4.SubSubsidaryAccountCode
    left join TBLCRVMaster cr
        on cr.SubSubsidaryAccountId=lvl4_2.SubSubsidaryAccountCode
group by lvl4.SubSubsidaryAccountName

edited Mar 05 '15 at 20:50

answered Mar 05 '15 at 20:38

G B

1,412
10
12

thank you for your rply ,,, according to this way it only gets the two same accounts in both tables and does not display the differnt accounts – sheikh muzammil Mar 05 '15 at 20:45
If I understand your comment you want all accounts whether they have had payments or not, try the above with the edits I have made. – G B Mar 05 '15 at 20:51
thanks again man!! u get my point.. i have to create a report of receipt and payment accounts that's why i need two queries with two account names to display seprately receipt account as well as payment account – sheikh muzammil Mar 05 '15 at 21:20

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

2 Answers2