Link to home
Start Free TrialLog in
Avatar of W D
W DFlag for United States of America

asked on

Oracle SQL: smerge two similar tables having a difference of one column

Hi,
I'd like to smerge two tables that are almost alike: InventoryUsed, InventoryWasted
They both have Product_ID. InventoryUsed has a Inventory_Used column that I will sum per Product_ID. InventoryWasted has a Inventory_Wasted column that I will sum per Product_ID.

How do I smerge these two queries:
SELECT Product_ID, SUM(Inventory_Used)
FROM InventoryUsed
GROUP BY Product_ID

SELECT Product_ID, SUM(Inventory_Wasted)
FROM InventoryWasted
GROUP BY Product_ID

So that I get:
Product_ID, SUM(Inventory_Used), SUM(Inventory_Wasted)

Best regards,
WDelaney





Avatar of ajexpert
ajexpert
Flag of United States of America image

Will this work for you?

SELECT   IU.Product_ID,
           SUM (IU.Inventory_Used) Inventory_used,
           SUM (Inventory_Wasted) Inventory_wasted
    FROM   InventoryUsed IU, InventoryWasted IW
   WHERE   IU.PRODUCT_ID = IW.PRODUCT_ID
GROUP BY   IU.Product_ID

Open in new window

Avatar of W D

ASKER

And what if a Product_ID is in Inventory_Used but not in Inventory_Wasted? And vice versa? I can use the Product table (has Product_ID) and LEFT OUTER JOIN with both of the summary tables - what do you think?
Yes. You got it right
Avatar of W D

ASKER

Would a UNION work with this?
ASKER CERTIFIED SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W D

ASKER

What does the plus sign mean?
its way of representing the LEFT or RIGHT outer join in Oracle
Avatar of W D

ASKER

Ah. I see. Thanks!
I just ran both SQL statements and they both work, thanks very much!
If you want to use union, try the attached.
query.txt