We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

combine two select stmts to one outcome

fahVB
fahVB asked
on
Medium Priority
311 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

Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
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.

Author

Commented:
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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Author

Commented:
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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Author

Commented:
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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Ok, we just have to put back a proper sort order, give me a minute
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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 WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Author

Commented:
Thank you
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Author

Commented:
Awesome, this worked..
Thank you so much
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.