Problem with using SUM(X*Y) where x and y come from different tables

[slyry88]

I am trying to use this syntax to no avail:

SELECT C_Name, ORDERS.O_ID, SUM(ITEM_ORDERED.QUANTITY*PRODUCT.P_PRICE) AS TOTAL_PRICE
FROM CUSTOMER, ORDERS, ITEM_ORDERED, PRODUCT
WHERE CUSTOMER.C_ID=ORDERS.C_ID AND ORDERS.O_ID=ITEM_ORDERED.O_ID AND ITEM_ORDERED.P_ID=PRODUCT.P_ID
GROUP BY ORDERS.O_ID;

My 4 tables would look like this:

customer order item_ordered Product
PK C_ID PK O_ID PK,FK O_ID PK P_ID
C_Name FK C_ID PK,FK P_ID P_Price

Quantity is also in the item_ordered table.

I have attached a better representation of my tables if that will help.

The problem is that I have not been able to calculate the sum of each order. If I take out the GROUP BY and SUM from my current syntax that leaves me with the correct totals, but they are not grouped by O_ID.

Is there a reason I cannot do SUM(X*Y) when they come from different tables?

[CPlusPlusGeniou]

Well since this is in the oracle database part of the forum..I'm going to assume your using oracle.

If you using an aggregate function such as SUM, you need to GROUP BY all columns so the syntax would just be.

1. SELECT C_Name, ORDERS.O_ID, SUM(ITEM_ORDERED.QUANTITY*PRODUCT.P_PRICE) AS TOTAL_PRICE
   2. FROM CUSTOMER, ORDERS, ITEM_ORDERED, PRODUCT
   3. WHERE CUSTOMER.C_ID=ORDERS.C_ID AND ORDERS.O_ID=ITEM_ORDERED.O_ID AND ITEM_ORDERED.P_ID=PRODUCT.P_ID
   4. GROUP BY ORDERS.O_ID, CUSTOMER.C_NAME;

Hope this helps!