We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

## 16 replies

Last post Aug 29, 2018 12:49 AM by kengkit

• kengkit

Member

103 Points

796 Posts

### show two query results into one?

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

• vahid bakkhi

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

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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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

• vahid bakkhi

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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

Hi,

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

• vahid bakkhi

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?

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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

• vahid bakkhi

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

`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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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

• vahid bakkhi

Star

8119 Points

2778 Posts

### Re: show two query results into one?

Aug 28, 2018 10:51 AM|vahid bakkhi|LINK

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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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`

• vahid bakkhi

Star

8119 Points

2778 Posts

### Re: show two query results into one?

Aug 28, 2018 11:20 AM|vahid bakkhi|LINK

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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

• vahid bakkhi

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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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?
• vahid bakkhi

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

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

vahid bakhtiary

• kengkit

Member

103 Points

796 Posts

### Re: show two query results into one?

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