CODEWITHSUNDEEP
sqloracle-databaseunion
Imran Hemani
Imran Hemani

Reputation: 629

Oracle SQL - adding in two union queries

I have to queries on which I am applying a union function.

  select item, loc, qty from rms_transfer
  union
  select item, loc, qty from sim_transfer

wherever item and loc are same in both the queries, the qty of both the queries should add up in in the final result.

How do we achieve that ?

Upvotes: 2

Views: 1814

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you want union all, not union:

select item, loc, qty from rms_transfer;
union all
select item, loc, qty from sim_transfer;

union removes duplicates. So, if you want to keep all the original rows, then use union all.

If you want the values on the same row, then you can use a post-aggregation:

select item, loc, sum(qty)
from (select item, loc, qty from rms_transfer;
      union all
      select item, loc, qty from sim_transfer
     ) il
group by item, loc

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Just do a GROUP BY:

SELECT item, loc, SUM(qty) AS qty
FROM (
  SELECT item, loc, qty FROM rms_transfer
  union
  SELECT item, loc, qty FROM sim_transfer) AS t
GROUP BY item, loc

Same item, loc pairs will be grouped together and their corresponding quantities will be summed up.

Upvotes: 2

Related Questions