CODEWITHSUNDEEP
sqloracle-database
Zelkop
Zelkop

Reputation: 109

Oracle SQL group by not functioning correctly

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;

enter image description here

Upvotes: 0

Views: 111

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 1

Related Questions