asked on

combine two select stmts to one outcome

I need to combine these 2 stmts so the outcome looks like this, these are 2 different select stmts and when i run it through scheduler only the first select runs, i can run it manually in text format and the outcome looks exactly what i need...

loan_amount Loan_Program IntRate
-------------- ------------------------------------- ---------
6855700.50 Advantage 3/3 ARM 4.98
450300.00 Advantage 5/1 ARM 5.41
657000.00 Conforming Fixed Rate 10 Year 3.71
3320750.00 Conforming Fixed Rate 15 Year 4.37
407000.00 Conforming Fixed Rate 20 Year 4.88
6493450.00 Conforming Fixed Rate 30 Year 5.17
4297530.00 Nonconforming Fixed Rate 10 Year 3.99
11562963.00 Nonconforming Fixed Rate 15 Year 4.49

GrandTotal Avg
------------------- --------------
34044693.50 4.645500

select loan_amount,Test,Loan_Program,IntRate
from PipeLineSummary
order by 2
select sum(loan_amount) as GrandTotal, sum(Test)/sum(loan_amount) as Avg from PipeLineSummary

Open in new window

Ephraim Wangoya

use union all

select loan_amount,Test,Loan_Program,IntRate
from PipeLineSummary
union all
select sum(loan_amount), '', sum(Test)/sum(loan_amount) as Avg from PipeLineSummary

jimyX

Could you try this one please:
select loan_amount,Test,Loan_Program,IntRate
from PipeLineSummary
order by 2
union
select sum(loan_amount) as loan_amount, sum(Test)/sum(loan_amount) as Test, ' ' as Loan_Program, ' ' as IntRate from PipeLineSummary

fahVB

ASKER

JimmyX
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
and i cant use order by with your suggestions

ewangoya
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

fahVB

ASKER

I need the outcome look like this please...

loan_amount Loan_Program IntRate
-------------- ------------------------------------- ---------
6855700.50 Advantage 3/3 ARM 4.98
450300.00 Advantage 5/1 ARM 5.41
657000.00 Conforming Fixed Rate 10 Year 3.71
3320750.00 Conforming Fixed Rate 15 Year 4.37
407000.00 Conforming Fixed Rate 20 Year 4.88
6493450.00 Conforming Fixed Rate 30 Year 5.17
4297530.00 Nonconforming Fixed Rate 10 Year 3.99
11562963.00 Nonconforming Fixed Rate 15 Year 4.49

GrandTotal Avg
------------------- --------------
34044693.50 4.645500

Ephraim Wangoya

Change it to

select loan_amount,Test,Loan_Program,IntRate
from PipeLineSummary
union all
select sum(loan_amount), '', '', sum(Test)/sum(loan_amount) from PipeLineSummary

Ephraim Wangoya

But the results you are showing dont match your query
What is the field Test?

fahVB

ASKER

getting same
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

loan_amount Test Loan_Program IntRate
--------------------------------------- --------------------------------------- ---------------------------------------- ---------------------------------------
200300.00 1093812.50 Advantage 5/1 ARM 5.46
407000.00 1984125.00 Conforming Fixed Rate 20 Year 4.88
657000.00 2434750.00 Conforming Fixed Rate 10 Year 3.71
3320750.00 14516781.25 Conforming Fixed Rate 15 Year 4.37
4297530.00 17147144.70 Nonconforming Fixed Rate 10 Year 3.99
6493450.00 33557343.75 Conforming Fixed Rate 30 Year 5.17
6855700.50 34159241.13 Advantage 3/3 ARM 4.98
11562963.00 51917703.87 Nonconforming Fixed Rate 15 Year 4.49

(8 row(s) affected)

GrandTotal Avg
--------------------------------------- ---------------------------------------
33794693.50 4.640104

(1 row(s) affected)

Ephraim Wangoya

Without the test field, use this

select Loan_Program, loan_amount, IntRate
from PipeLineSummary
union all
select 'Grand Total', sum(loan_amount), null from PipeLineSummary
union
select 'Average', sum(Test)/sum(loan_amount), null from PipeLineSummary

Ephraim Wangoya

Ok, Test is numeric so you can use 0 instead

select Loan_Program, loan_amount, test, IntRate
from PipeLineSummary
union all
select 'Grand Total', sum(loan_amount), null, null from PipeLineSummary
union
select 'Average', sum(Test)/sum(loan_amount), null, null from PipeLineSummary

fahVB

ASKER

See how Grand total and Avg are dropping in the middle...

Loan_Program loan_amount IntRate
---------------------------------------- --------------------------------------- ---------------------------------------
Advantage 3/3 ARM 6855700.50 4.98
Advantage 5/1 ARM 200300.00 5.46
Average 4.64 NULL
Conforming Fixed Rate 10 Year 657000.00 3.71
Conforming Fixed Rate 15 Year 3320750.00 4.37
Conforming Fixed Rate 20 Year 407000.00 4.88
Conforming Fixed Rate 30 Year 6493450.00 5.17
Grand Total 33794693.50 NULL
Nonconforming Fixed Rate 10 Year 4297530.00 3.99
Nonconforming Fixed Rate 15 Year 11562963.00 4.49

(10 row(s) affected)

Ephraim Wangoya

Ok, we just have to put back a proper sort order, give me a minute

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fahVB

ASKER

Why cant I use order by here,

select Loan_Program, loan_amount, test, IntRate
from PipeLineSummary
order by 2
union all
select 'Grand Total', sum(loan_amount), null, null from PipeLineSummary
union all
select 'Average', sum(Test)/sum(loan_amount), null, null from PipeLineSummary

Ephraim Wangoya

Union statement does not allow you to do that, you can have a way to work around that by assigning a row index

fahVB

ASKER

Thank you

Ephraim Wangoya

try this

select [Loan Program], [Loan Amount], test, [Interest Rate]
from
(
      select 1 ColIndex, Loan_Program [Loan Program], loan_amount [Loan Amount], test, IntRate [Interest Rate]
      from PipeLineSummary
      union all
      select 2, 'Grand Total', sum(loan_amount), null, null from PipeLineSummary
      union all
      select 3, 'Average', sum(Test)/sum(loan_amount), null, null from PipeLineSummary
) Result
order by ColIndex, 2

fahVB

ASKER

Awesome, this worked..
Thank you so much