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?