Oracle SQL - adding in two union queries

Question

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 ?

Sample data and desired results would clarify what you want to do. — Gordon Linoff
– Gordon Linoff, Commented Feb 15, 2016 at 14:18
There is a general flaw in using 'union' here, because it skips rows from the second query that are already part of the first one. You have to use a 'union all' for this. The needed grouping is already mentioned within the answers so I skip it here. — wumpz
– wumpz, Commented Feb 15, 2016 at 14:26
Are item + loc unique in the two tables or can I find several rows for an item + loc in one of the tables? — Thorsten Kettner
– Thorsten Kettner, Commented Feb 15, 2016 at 14:32

Gordon Linoff · Accepted Answer · 2016-02-15 14:25:23Z

4

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

edited Feb 15, 2016 at 14:25

answered Feb 15, 2016 at 14:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giorgos Betsos · Accepted Answer · 2016-02-15 14:17:32Z

2

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.

answered Feb 15, 2016 at 14:17

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

2 Comments

user330315 Over a year ago

union will remove duplicate rows between the two tables. So they won't be counted twice which is - I think - what Imran wants

Jorge Vega Sánchez Over a year ago

Great answer and combined with union all instead of simple union, solved my challenge.

Collectives™ on Stack Overflow

Oracle SQL - adding in two union queries

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related