Home » SQL & PL/SQL » SQL & PL/SQL » problem in union sql statment (9i)
problem in union sql statment [message #416656] Mon, 03 August 2009 14:21 Go to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Hii all

I have a problem in this statement
select item_code , sum(qty_total) qty_total
from onhand_view_jul
where item_code ='sunshine'
group by item_code
union
select item_code , sum(s_qty+rs_qty) qty_total
from trx_headers h , trx_lines l , items i
where i.item_code = l.item_code
and h.trx_header_id = l.trx_header_id
and item_code ='sunshine'
group by item_code



this query has this result
item_code    qty_total
sunshine       100
sunshine       200



i don't want this result
i want it like that
item_code    qty_total
sunshine       300


how can i do this?

Thanks
Re: problem in union sql statment [message #416657 is a reply to message #416656] Mon, 03 August 2009 14:23 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select item_code, sum(qty_total) total 
from (your current query here)
group by item_code

[Updated on: Mon, 03 August 2009 14:23]

Report message to a moderator

Re: problem in union sql statment [message #416658 is a reply to message #416656] Mon, 03 August 2009 14:23 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>how can i do this?
do not use UNION or "wrap" as a subselect
Re: problem in union sql statment [message #416662 is a reply to message #416656] Mon, 03 August 2009 14:51 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Thanks for your replies

i know that i can use this
select item_code, sum(qty_total) total 
from (your current query here)
group by item_code


but please why the my query didnot return my expected result
is there any thing wrong?

isnot this is the function of the UNION
to the row not repeated?

thanks
Re: problem in union sql statment [message #416663 is a reply to message #416656] Mon, 03 August 2009 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>isnot this is the function of the UNION to the row not repeated?
The returned rows were not repeated. The returned rows were different; not duplicates.
Re: problem in union sql statment [message #416697 is a reply to message #416656] Tue, 04 August 2009 00:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Moreover, UNION is not used for summation. It is set operator, not mathematical function.

regards,
Delna
Re: problem in union sql statment [message #416749 is a reply to message #416656] Tue, 04 August 2009 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also, if you're going to follow Littlefoot's suggestion you need to change the UNION to UNION ALL, you don't want it to do a distinct in this case.
Re: problem in union sql statment [message #416770 is a reply to message #416697] Tue, 04 August 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Tue, 04 August 2009 07:22
Moreover, UNION is not used for summation. It is set operator, not mathematical function.

regards,
Delna

And in this case it is the sum of result from 2 result sets. How do you get it without union (all)?

Regards
Michel

Re: problem in union sql statment [message #416781 is a reply to message #416770] Tue, 04 August 2009 05:20 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Michel sir,
I didn't said that OP must not use UNION. I was intended to say OP should use UNION with SUM as suggested by Littlefoot sir.
OP was expecting that UNION will do SUM of two result sets.
And I corrected it by
Quote:
Moreover, UNION is not used for summation. It is set operator, not mathematical function.


regards,
Delna
Previous Topic: Getting the error 'ORA-01732' while removing Duplicates
Next Topic: How to measure the appox. proper exection time of query (merged)
Goto Forum:
  


Current Time: Tue Dec 06 16:06:17 CST 2016

Total time taken to generate the page: 0.06147 seconds