?
Solved

combine two select stmts to one outcome

Posted on 2011-04-21
17
Medium Priority
?
299 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:fahVB
  • 9
  • 7
17 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442021

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
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35442022
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
0
 

Author Comment

by:fahVB
ID: 35442198
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:fahVB
ID: 35442213
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442226

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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442232

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

Author Comment

by:fahVB
ID: 35442249
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)

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442277
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442298

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
0
 

Author Comment

by:fahVB
ID: 35442374
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)

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442394

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

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35442421

Oh My bad use union all for the last statement

select Loan_Program, loan_amount, test, IntRate
from PipeLineSummary
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
0
 

Author Comment

by:fahVB
ID: 35442442
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442507

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

Author Closing Comment

by:fahVB
ID: 35442519
Thank you
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35442547

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
0
 

Author Comment

by:fahVB
ID: 35442682
Awesome, this worked..
Thank you so much
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question