Any way to unify this 2 selects? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Any way to unify this 2 selects?

Author

Topic

adeljin
Starting Member

4 Posts

Posted - 2009-11-04 : 06:12:25

Hello,

First of all, sorry for my english, I know it isn't perfect :P Hopefully u'll understand me :)

I have this 2 SQL selects:

select
m.nombre||' '||e.ejercicio mes, t.tipoobra,
count(*) num_aceptadas,
sum((select (valor) from TotObra(o.obra))) total_aceptado from
ejercicio e, meses m, tipoobra t, obra o where
t.tipoobra=o.tipoobra and
m.mes=extract(month from o.fechaacepta) and
e.ejercicio=extract(year from o.fechaacepta) and
e.ejercicio=:EJERCICIO and (m.mes between 1 and 12) group by m.mes, m.nombre, e.ejercicio, t.tipoobra order by e.ejercicio, m.mes, t.tipoobra

and this one:

select
m.nombre||' '||e.ejercicio mes, t.tipoobra,
count(*) num_ofertas,
sum((select (valor) from TotObra(o.obra))) total_ofertado from
ejercicio e, meses m, tipoobra t, obra o where
t.tipoobra=o.tipoobra and
m.mes=extract(month from o.fechaalta) and
e.ejercicio=extract(year from o.fechaalta) and
e.ejercicio=:EJERCICIO and (m.mes between 1 and 12) group by m.mes, m.nombre, e.ejercicio, t.tipoobra order by e.ejercicio, m.mes, t.tipoobra

Do you guys know how could I unify those 2 selects into 1? Both are taking info from the same tables, so I guess it's just a matter of variable names...

Anyways, thanks in advance for your help, hope you can give me a hand :)

A.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-04 : 06:15:21

use union all or union

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-04 : 06:16:46

Try in Oracle forum you may get some instant help..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

adeljin
Starting Member

4 Posts

Posted - 2009-11-04 : 07:06:23

quote:
Originally posted by ashishashish

use union all or union

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

This kinda worked, thanks :) However, i've got a problem. As you can see watching the SQL quotes, there should be a column for total_aceptado and a column for total_ofertado. However, this is the result I get after doing the UNION:

It just shows the two results one after the other, but I need to separate them in order to see one for the total_aceptado ones and the other one for the total_ofertado ones.

Do you know how could I change my SQL code to accomplish that?

Thanks again!

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-04 : 07:59:23

well what i understand from your explanation is that you want some thing like this...

MES TIPOOBRA NUMACEPTADAS NUMOFERTAS TOTALACEPTADAS TOTALOFERTAS
-----------------------------------------------------------------------------------------
Abril2009 1 10 21 6090 11930

IF that you want then

DO Something like that

SELECT A.MES, A.TIPOOBRA, A.NUMACEPTADAS, B.NUMOFERTAS, A.TOTALACEPTADAS, B.TOTALOFERTAS FROM
(YOUR SELECT STATEMENT ) A
JOIN
(YOUR SELECT STATEMENT ) B
on A.MES=B.MES
AND A.TIPOOBRA=B.TIPOOBRA

Hope this will work.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

adeljin
Starting Member

4 Posts

Posted - 2009-11-05 : 06:30:00

quote:
Originally posted by ashishashish

well what i understand from your explanation is that you want some thing like this...

MES TIPOOBRA NUMACEPTADAS NUMOFERTAS TOTALACEPTADAS TOTALOFERTAS
-----------------------------------------------------------------------------------------
Abril2009 1 10 21 6090 11930

IF that you want then

DO Something like that

SELECT A.MES, A.TIPOOBRA, A.NUMACEPTADAS, B.NUMOFERTAS, A.TOTALACEPTADAS, B.TOTALOFERTAS FROM
(YOUR SELECT STATEMENT ) A
JOIN
(YOUR SELECT STATEMENT ) B
on A.MES=B.MES
AND A.TIPOOBRA=B.TIPOOBRA

Hope this will work.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Yeah, thats exactly what I'm trying to do :) And it worked!!!

Thanks a lot Ashi!

Edit: Mmmm, one last problem I have. With the result of that select I don't know how to order the result by the "mes" (month) column. After watching the 2 separate selects I used in your code, do you know how could I order it again?

This is how it was ordered when using only one select. Now that I use those 2 selects, I can't order them anymore :/

Thanks!

adeljin
Starting Member

4 Posts

Posted - 2009-11-11 : 06:39:39

up!

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-11 : 07:02:57

SELECT A.MES, A.TIPOOBRA, A.NUMACEPTADAS, B.NUMOFERTAS, A.TOTALACEPTADAS, B.TOTALOFERTAS FROM
(YOUR SELECT STATEMENT ) A
JOIN
(YOUR SELECT STATEMENT ) B
on A.MES=B.MES
AND A.TIPOOBRA=B.TIPOOBRA ORDER by A.MES

TRY LILKE THIS

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources