When I try to group this by name and id, it still shows duplicates. How could I combine these 5 names into one?
SELECT C.CUSTOMER_ID, C.NAME, OI.ORDER_ID, O.ORDER_DATE, SUM(OI.QUANTITY), SUM(OI.QUANTITY * OI.UNIT_PRICE)
FROM CUSTOMERS C
INNER JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
INNER JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
WHERE C.CUSTOMER_ID = 44
GROUP BY OI.ORDER_ID, O.ORDER_DATE, C.CUSTOMER_ID, C.NAME
ORDER BY SUM(OI.QUANTITY * OI.UNIT_PRICE) DESC;
I am guessing you want one row per customer:
SELECT C.CUSTOMER_ID, C.NAME, SUM(OI.QUANTITY), SUM(OI.QUANTITY * OI.UNIT_PRICE)
FROM CUSTOMERS C INNER JOIN
ORDERS O
ON C.CUSTOMER_ID = O.CUSTOMER_ID INNER JOIN
ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
WHERE C.CUSTOMER_ID = 44
GROUP BY C.CUSTOMER_ID, C.NAME
ORDER BY SUM(OI.QUANTITY * OI.UNIT_PRICE) DESC;
The columns more specific to the orders should not be in the SELECT or GROUP BY.
As pointed out above, you're getting what you asked for. My best guess, however, is that you want the individual name, ID, and Order date fields, but that you then want a subtotal for each customer. The way to do this is with ROLLUP:
SELECT *
FROM (SELECT C.CUSTOMER_ID, C.NAME, OI.ORDER_ID, O.ORDER_DATE,
SUM(OI.QUANTITY) AS ITEM_QUANTITY,
SUM(OI.QUANTITY * OI.UNIT_PRICE) AS EXTENDED_PRICE
FROM CUSTOMERS C
INNER JOIN ORDERS O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
INNER JOIN ORDER_ITEMS OI
ON O.ORDER_ID = OI.ORDER_ID
WHERE C.CUSTOMER_ID = 44
GROUP BY ROLLUP (C.CUSTOMER_ID, C.NAME, OI.ORDER_ID, O.ORDER_DATE))
WHERE CUSTOMER_ID IS NOT NULL AND
NAME IS NOT NULL AND
(ORDER_DATE IS NOT NULL OR
(ORDER_DATE IS NULL AND ORDER_ID IS NULL))
ORDER BY CUSTOMER_ID, NAME, EXTENDED_PRICE DESC
This produces a result of
CUSTOMER_ID NAME ORDER_ID ORDER_DATE ITEM_QUANTITY EXTENDED_PRICE
44 Jabil Circuit 3772 3334311.72
44 Jabil Circuit 92 28-AUG-15 790 1050939.97
44 Jabil Circuit 69 17-MAR-17 581 755093.92
44 Jabil Circuit 10 24-JAN-17 883 621052.99
44 Jabil Circuit 29 14-AUG-17 831 508588.59
44 Jabil Circuit 82 16-DEC-16 687 398636.25
The WHERE clause in the outer query just gets rid of some unwanted sub-total lines - try taking the WHERE clause out to see what it eliminates from the result set.
