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
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
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
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 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
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
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
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
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
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
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
Member
103 Points
796 Posts
show two query results into one?
Aug 27, 2018 03:20 AM|kengkit|LINK
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
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 27, 2018 04:12 AM|vahid bakkhi|LINK
hi
You can aliasing both query and Selecting them in the select query below like
now your query
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 09:25 AM|kengkit|LINK
Hi, almost done. please advise. TQ
My Query:
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
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 09:36 AM|vahid bakkhi|LINK
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 yPlease MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 09:52 AM|kengkit|LINK
Hi,
The result returned the same. Any idea to solve it? TQ~
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 09:57 AM|vahid bakkhi|LINK
can you describe how to you filter them?
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 10:01 AM|kengkit|LINK
Hi,
I follow ur suggestion. use Distinct
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 10:09 AM|vahid bakkhi|LINK
yes, Distinct remove duplicate rows, if they have the same data in columns you defined
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
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 10:21 AM|kengkit|LINK
Hi, anything wrong with it?
This query (x) returned.
1, 15, 1500
2, 45, 3280
3, 28, 6350
This query (y) returned.
1, 800
2, 550
3, 360
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
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 10:51 AM|vahid bakkhi|LINK
please try below query
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 11:18 AM|kengkit|LINK
Hi, thanks for your prompt reply.
But i got the following error message
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 11:20 AM|vahid bakkhi|LINK
please put here your complete query
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 11:23 AM|kengkit|LINK
hi,
i 100% follow your suggestion.
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 11:31 AM|vahid bakkhi|LINK
I tried it, it worked fine . you may have a mistake in your side
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 28, 2018 11:35 AM|kengkit|LINK
let me have a check and find out what’s wrong from my side?
Star
8119 Points
2778 Posts
Re: show two query results into one?
Aug 28, 2018 11:47 AM|vahid bakkhi|LINK
you can try below code, I created a temp table with below struct, first try it and then change to your query
Please MARK AS ANSWER if suggestion helps.
Member
103 Points
796 Posts
Re: show two query results into one?
Aug 29, 2018 12:49 AM|kengkit|LINK
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.