| grouping by 2 sql stts joined by UNION [message #19339] |
Tue, 12 March 2002 09:14  |
citrav
Messages: 7
Registered: October 2001
|
Junior Member |
|
|
Hi all,
I am joing two queries using a UNION operand.
Both the queries return person id. I want the final result (from both queries) grouped on basis of the person id.
select person_id, sales_amount as amount
from sales where <condition>
union
select person_id, purchase_amount as amount
from purchases where <condition>
from the above 2 queries i want the final result of person id and sum(amount) grouped by person_id
hwo can i get that in a single sql stt?
right now i am creating a view for the above sql stt
and then grouping them based on person id.
i do not want to create any views
can anybody help me in this regard?
thanks
|
|
|
|
| Re: grouping by 2 sql stts joined by UNION [message #19340 is a reply to message #19339] |
Tue, 12 March 2002 09:30  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Just use an inline view:
select person_id, sum(amount)
from (select person_id, sales_amount amount
from sales
union all
select person_id, purchase_amount amount
from purchases)
group by person_id
You will want to do a UNION ALL here so you do not lose any potential duplicates between your sales and purchases tables.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
