Grouping_sets getting different output formats when group by changed

Question

I have the following test CASE which is working fine.

When I try to group by customer_id, TO_CHAR (p.purchase_date, 'IYYY"W"IW') I'm not getting the same format as the working query. As a workaround I could limit the query to 1 customer_id each time with a where clause but I really don't want to do that.

Can someone please tell me what the problem is and how to rectify the issue.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;


CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;


CREATE TABLE purchases(
    PURCHASE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    CUSTOMER_ID NUMBER, 
    PRODUCT_ID NUMBER, 
    QUANTITY NUMBER, 
   PURCHASE_DATE TIMESTAMP
);

INSERT  INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) 
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

ALTER TABLE purchases 
ADD CONSTRAINT purchases_pk PRIMARY KEY (purchase_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);

/* works fine */

SELECT    TO_CHAR (p.purchase_date, 'IYYY"W"IW')    AS year_week
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'IYYY"W"IW'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id;

YEAR_WEEK	CUSTOMER_ID	FIRST_NAME	LAST_NAME	TOTAL_AMT
2022W41	1	Faith	Mazzarone	415.96
2022W41	2	Lisa	Saladino	111.99
2022W41	-	-	-	527.95
2022W42	2	Lisa	Saladino	335.97
2022W42	-	-	-	335.97
2022W49	2	Lisa	Saladino	65.94
2022W49	3	Micheal	Palmice	111.99
2022W49	-	-	-	177.93
2022W50	2	Lisa	Saladino	131.88
2022W50	3	Micheal	Palmice	142.87
2022W50	-	-	-	274.75
2022W51	3	Micheal	Palmice	243.87
2022W51	-	-	-	243.87
2022W52	2	Lisa	Saladino	98.91
2022W52	3	Micheal	Palmice	186.83
2022W52	-	-	-	285.74
2023W01	2	Lisa	Saladino	98.91
2023W01	3	Micheal	Palmice	131.88
2023W01	-	-	-	230.79
2023W02	3	Micheal	Palmice	175.84
2023W02	-	-	-	175.84
2023W03	3	Micheal	Palmice	131.88
2023W03	-	-	-	131.88
-	-	-	-	2384.72

/* unexpected output */

SELECT        p.customer_id,
      c.first_name,
      c.last_name,
TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS year_week,
      SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS ( 
    (p.customer_id, c.first_name, c.last_name),
    (TO_CHAR (p.purchase_date, 'IYYY"W"IW')), 
(p.customer_id, c.first_name, c.last_name)
, ()
)
ORDER BY  p.customer_id,
TO_CHAR (p.purchase_date, 'IYYY"W"IW');

Answer 1

SELECT        p.customer_id,
      c.first_name,
      c.last_name,
TO_CHAR (p.purchase_date, 'IYYY"W"IW') AS year_week,
      SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS ( 
    (p.customer_id, c.first_name, c.last_name,  (TO_CHAR (p.purchase_date, 'IYYY"W"IW')))
, p.customer_id 
, ()
)
ORDER BY  p.customer_id,
TO_CHAR (p.purchase_date, 'IYYY"W"IW');


CUSTOMER_ID FIRST_NAME  LAST_NAME   YEAR_WEEK   TOTAL_AMT
1   Faith   Mazzarone   2022W41 415.96
1    -   -   -  415.96
2   Lisa    Saladino    2022W41 111.99
2   Lisa    Saladino    2022W42 335.97
2   Lisa    Saladino    2022W49 65.94
2   Lisa    Saladino    2022W50 131.88
2   Lisa    Saladino    2022W52 98.91
2   Lisa    Saladino    2023W01 98.91
2    -   -   -  843.6
3   Micheal Palmice 2022W49 111.99
3   Micheal Palmice 2022W50 142.87
3   Micheal Palmice 2022W51 243.87
3   Micheal Palmice 2022W52 186.83
3   Micheal Palmice 2023W01 131.88
3   Micheal Palmice 2023W02 175.84
3   Micheal Palmice 2023W03 131.88
3    -   -   -  1125.16
 -   -   -   -  2384.72