show two query results into one?

• 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

• 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 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`

```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

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 Thursday, October 7, 2021 12:00 AM
Tuesday, August 28, 2018 10:51 AM
• User944339287 posted

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

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

hi,

```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