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');

If the first result set is what you want and it currently works for at least three customers then what is the issue? `grouping sets` is equivalent of `union all` of `group by`s specified as tuples. — astentx, Jan 20 '23 at 19:25
First query is grouped by DATE, customer_id. I want customer, date grouping to work — Beefstu, Jan 20 '23 at 19:45
Assuming names are attributes of `customer_id` and not relevant for grouping (are included for `group by` to work), the first query is grouped by `date, customer_id` **and then** grouped by `date` (rows with customer attributes marked as `-`). If you swap `date` and `customer_id`, it would return the same. Do you want to have totals for customers (with `date` excluded)? If so, then what's wrong with the second query? — astentx, Jan 20 '23 at 19:53
No I want the dates included. I want the same information in the first query I posted. If I exclude the dates it defeats the purpose — Beefstu, Jan 20 '23 at 21:03
I really don't understand the issue. You have a query that shows some result that satisfies your need. But you want to write another query that returns the same. What is the purpose of this? What should be different? If you want to swap output columns, then just reorder select list — astentx, Jan 20 '23 at 21:17
What don't you understand I want the information to be grouped differently by customer_id, date. The first query works perfectly and groups by date, customer_id — Beefstu, Jan 20 '23 at 21:57
Grouping by date and customer is the same as grouping by customer and date because the same pair of customer and date (regardless of the order, this tuple has named domains) will fall into the same group. Please post your desired output and describe in general words what is wrong with your current output. If the current output (given in the table in the question) is what you want, then it's not clear what is the issue. Possibly I misunderstood your terminology of grouping: is it a display of the data or a `group by` operator itself? — astentx, Jan 20 '23 at 22:12
I figured it out and answered my own question. See last post — Beefstu, Jan 21 '23 at 00:06

score 0 · Answer 1 · answered Jan 21 '23 at 00:05


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

Grouping_sets getting different output formats when group by changed

1 Answers1