CODEWITHSUNDEEP
oracle-database
dem.1797
dem.1797

Reputation: 49

JOIN of 3 tables and SUM ORACLE

I have 3 tables:

Product:
+----------------------------------------+
| ID_product | name_product |   Amount   |
+----------------------------------------+
|     0      |    Door      |    450     |
+----------------------------------------+
|     1      |    Fence     |    1500    |
+----------------------------------------+

Operation:
+----------------------------------------+
| ID_operation | name_operation |  cost  |
+----------------------------------------+
|      0       |     Repair     |   250  |
+----------------------------------------+
|      1       |     Build      |   320  |
+----------------------------------------+

Process:
+----------------------------------------+
|    ID_product   |    ID_operation      |
+----------------------------------------+
|       0         |          0           |
+----------------------------------------+
|       0         |          1           |
+----------------------------------------+
|       1         |          0           |
+----------------------------------------+
|       1         |          1           |
+----------------------------------------+

And need to calculate the sum of costs for each product like this:

    Result table:
   +-----------------------------------+
   |  name_product  |    TOTAL_COSTS   |
   +-----------------------------------+
   |      Door      |   570 (250+320)  |
   +-----------------------------------+
   |      Fence     |        570       |
   +-----------------------------------+

But i don't have any idea how. I think I need some JOINS like below but I don't know how to handle the sum.

SELECT name_product, operation.cost
FROM product
JOIN process ON product.ID_product = process.ID_product
JOIN operation ON operation.ID_operation = process.ID_operation
ORDER BY product.ID_product;

Upvotes: 0

Views: 48

Answers (2)

Badrul
Badrul

Reputation: 1139

Try the below Query

SELECT P.NAME_PRODUCT,SUM(O.COST)COST
FROM PROCESS PR,PRODUCT P,OPERATION O
WHERE PR.ID_PRODUCT=P.ID_PRODUCT
AND PR.ID_OPERATION=O.ID_OPERATION
GROUP BY P.NAME_PRODUCT;

Upvotes: 1

GMB
GMB

Reputation: 222412

You are almost there. Your JOINs are OK, you just need to add a GROUP BY clause with aggregate function SUM.

SELECT product.name_product, SUM(operation.cost) total_costs
FROM product
JOIN process ON product.ID_product = process.ID_product
JOIN operation ON operation.ID_operation = process.ID_operation
GROUP BY product.ID_product, product.name_product
ORDER BY product.ID_product;

Upvotes: 0

Related Questions