locked
show two query results into one? RRS feed

  • Question

  • User944339287 posted

    Hi guys.. How can i show two query results into one?

    1) SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)

    returned result for first query
    [Month], [Count], [CapitalAmount]
    1, 32, 280
    2, 13, 630
    3, 25, 400

    2) SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)

    returned result for second query
    [Month], [CollectedAmount]
    1, 500
    2, 800
    3, 650

    Expected Returned Result in 1 query.
    [Month], [Count], [CapitalAmount], [CollectedAmount]
    1, 32, 280, 500
    2, 13, 630, 800
    3, 25, 400, 650

    Monday, August 27, 2018 3:20 AM

Answers

  • User-369506445 posted

    please try below query

    select *,(select top 1 CollectedAmount from ( SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date) ) as l where l.[Month]=x.Month) as CollectedAmount
     from 
    (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2018 10:51 AM

All replies

  • User-369506445 posted

    hi

    You can aliasing both query and Selecting them in the select query below like

    SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

    now your query

    select x.[Month],x.[count],x.[CapitalAmount],CollectedAmount
    FROM (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    , (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)) as y

    Monday, August 27, 2018 4:12 AM
  • User944339287 posted

    Hi, almost done. please advise. TQ

    My Query:

    SELECT x.[Month],x.[Count],x.[CapitalAmount],y.[CollectedAmount]
    FROM (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    , (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)) as y

    Expected Returned Result
    1, 15, 1500, 800
    2, 45, 3280, 550
    3, 28, 6350, 360

    Current Returned Result
    [Month], [Count], [CapitalAmount], [CollectedAmount]
    1, 15, 1500, 800
    2, 45, 3280, 800
    3, 28, 6350, 800
    1, 15, 1500, 550
    2, 45, 3280, 550
    3, 28, 6350, 550
    1, 15, 1500, 360
    2, 45, 3280, 360
    3, 28, 6350, 360

    Tuesday, August 28, 2018 9:25 AM
  • User-369506445 posted

    you can use Distinct keyword below like

     SELECT distinct x.[Month],x.[Count],x.[CapitalAmount],y.[CollectedAmount]
    FROM (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    , (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)) as y

    Tuesday, August 28, 2018 9:36 AM
  • User944339287 posted

    Hi,

    The result returned the same. Any idea to solve it? TQ~

    Tuesday, August 28, 2018 9:52 AM
  • User-369506445 posted

    can you describe how <g class="gr_ gr_15 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="15" data-gr-id="15">to</g> you filter them?

    Tuesday, August 28, 2018 9:57 AM
  • User944339287 posted

    Hi,

    I follow ur suggestion. use Distinct

    SELECT DISTINCT x.[Month],x.[Count],x.[CapitalAmount],y.[CollectedAmount]
    FROM (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    , (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)) as y



    Tuesday, August 28, 2018 10:01 AM
  • User-369506445 posted

    yes, Distinct remove duplicate rows, if they have the same data in columns you defined  

    SELECT DISTINCT x.[Month],x.[Count],x.[CapitalAmount],y.[CollectedAmount]

    but I think your scenario for filtering is different, you want to remove the duplicate row in the first table and shows the value of the second table sequence 

    Tuesday, August 28, 2018 10:09 AM
  • User944339287 posted

    Hi, anything wrong with it?

    SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)

    This query (x) returned.
    1, 15, 1500
    2, 45, 3280
    3, 28, 6350

    SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)

    This query (y) returned.
    1, 800
    2, 550
    3, 360

    SELECT DISTINCT x.Month,x.Count,x.CapitalAmount,y.CollectedAmount
    FROM (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    , (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date)) as y

    But this query returned
    1, 15, 1500, 800
    2, 45, 3280, 800
    3, 28, 6350, 800
    1, 15, 1500, 550
    2, 45, 3280, 550
    3, 28, 6350, 550
    1, 15, 1500, 360
    2, 45, 3280, 360
    3, 28, 6350, 360







    Tuesday, August 28, 2018 10:21 AM
  • User-369506445 posted

    please try below query

    select *,(select top 1 CollectedAmount from ( SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date) ) as l where l.[Month]=x.Month) as CollectedAmount
     from 
    (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2018 10:51 AM
  • User944339287 posted

    Hi, thanks for your prompt reply.

    But i got the following error message

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 CollectedAmount FROM (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as Colle' at line 1



    Tuesday, August 28, 2018 11:18 AM
  • User-369506445 posted

    please put here your complete query

    Tuesday, August 28, 2018 11:20 AM
  • User944339287 posted

    hi,

    i 100% follow your suggestion.

    select *,(select top 1 CollectedAmount from (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date) ) as l where l.[Month]=x.Month) as CollectedAmount
     from 
    (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x



    Tuesday, August 28, 2018 11:23 AM
  • User-369506445 posted

    I tried it, it worked <g class="gr_ gr_27 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="27" data-gr-id="27">fine .</g> you may have a mistake in your side

    Tuesday, August 28, 2018 11:31 AM
  • User944339287 posted
    hi.. may i have ur testing data? like structure.. dummy data and etc.
    let me have a check and find out what’s wrong from my side?
    Tuesday, August 28, 2018 11:35 AM
  • User-369506445 posted

    you can try below code, I created a temp table with below struct, first try it and then change to your query 

    create table #customer_account 
    (
    [Month] int , 
    [Count] int , 
    [CapitalAmount] int 
    ) 
    
    insert into #customer_account values(1,15,1500)
    insert into #customer_account values(2,45,3280)
    insert into #customer_account values(3,28,6350)
    
    
    create table #loan_general  
    (
    [Month] int , 
    [CollectedAmount ] int 
    ) 
    
    insert into #loan_general values(1,800)
    insert into #loan_general values(2,550)
    insert into #loan_general values(3,360)
     
    
    select *,(select top 1 CollectedAmount  from #loan_general l where c.[month]=l.[month] ) as CollectedAmount  from  #customer_account c

    Tuesday, August 28, 2018 11:47 AM
  • User944339287 posted

    hi,

    I have found a solution by referring your suggestion. The following sql statement work as expected. TQ

    * i put x.Month = y.Month at the end.

    select *,(select top 1 CollectedAmount from (SELECT MONTH(paid_date) as Month, SUM(paid_amt) as CollectedAmount FROM loan_general GROUP BY MONTH(paid_date) ) as l where l.[Month]=x.Month) as CollectedAmount
     from 
    (SELECT MONTH(start_date) as Month, COUNT(ID) as Count, SUM(capital) as CapitalAmount FROM customer_account GROUP BY MONTH(start_date)) as x WHERE x.month = y.month

    Wednesday, August 29, 2018 12:49 AM