Member Avatar for 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?

tables.doc (168 KB)
  • 2 Contributors
  • 1 Reply
  • 117 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by CPlusPlusGeniou

Dani AI

Generated

Short answer: summing a product across tables is fine — the issue is grouping and join behavior. is pointing in the right direction: any non-aggregated column you select must be resolved (either included in GROUP BY or produced by a prior aggregation). A cleaner pattern is to compute the per-order total first, then join that result to ORDERS and CUSTOMER so you do not have to GROUP BY a textual name.

Example (aggregate per order in a derived table, using explicit JOINs and NVL to guard nulls):

SELECT o.o_id,
       c.c_name,
       totals.total_price
FROM orders o
JOIN customer c ON o.c_id = c.c_id
JOIN (
  SELECT io.o_id,
         SUM(NVL(io.quantity,0) * NVL(p.p_price,0)) AS total_price
  FROM item_ordered io
  JOIN product p ON io.p_id = p.p_id
  GROUP BY io.o_id
) totals ON o.o_id = totals.o_id;

Checklist for debugging wrong totals

  • Inspect the raw join rows first (no aggregation) to ensure each ITEM_ORDERED row produces one price*quantity value.
  • Verify join cardinality: ITEM_ORDERED -> PRODUCT should be 1:1. Extra rows in PRODUCT or a missing join predicate will multiply totals.
  • Use NVL (or COALESCE) if price or quantity can be NULL; NULL * number yields NULL and is skipped by SUM.
  • Prefer grouping by the PK (o_id) or C_ID, not C_NAME — grouping by name can collapse distinct customers who share a name.
  • Use explicit JOIN syntax to avoid accidental cartesian products and to make the logic easier to read and maintain.

For large data sets, aggregating in the subquery reduces rows joined back to ORDERS/CUSTOMER and often improves performance.

Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.