I am trying to use the ROLLUP command to group my data by year/week, customer_id but I can't seem to get it to work.

Below is my sample data and my attempt. Can someone show me how to make this work

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
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
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 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;

SELECT 
      p.customer_id,
      c.first_name,
      c.last_name,
      sum(p.quantity * i.price) total_amt 
       FROM purchases p,
            items i,
            customers c
 WHERE p.customer_id = c.customer_id 
 GROUP BY ROLLUP    (to_char(p.purchase_date, 'YYYY/IW'),(p.customer_id))); 

CodePudding user response：

To me, it looks as

SQL>   SELECT p.customer_id,
  2           c.first_name,
  3           c.last_name,
  4           TO_CHAR (p.purchase_date, 'YYYY/IW') year_week,
  5           SUM (p.quantity * i.price) total_amt
  6      FROM purchases p, items i, customers c
  7     WHERE p.customer_id = c.customer_id
  8  GROUP BY c.first_name,
  9           c.last_name,
 10           p.customer_id,
 11           ROLLUP (TO_CHAR (p.purchase_date, 'YYYY/IW'))
 12  ORDER BY customer_id, year_week;

CUSTOMER_ID FIRST_N LAST_NAME YEAR_WE  TOTAL_AMT
----------- ------- --------- ------- ----------
          1 Faith   Mazzarone 2022/41     811,88
          1 Faith   Mazzarone             811,88
          2 Lisa    Saladino  2022/41     202,97
          2 Lisa    Saladino  2022/42     608,91
          2 Lisa    Saladino  2022/49    1217,82
          2 Lisa    Saladino  2022/50    2435,64
          2 Lisa    Saladino             4465,34
          3 Micheal Palmice   2022/49     202,97
          3 Micheal Palmice   2022/50    2638,61
          3 Micheal Palmice   2022/51    1623,76
          3 Micheal Palmice              4465,34

11 rows selected.

SQL>

CodePudding user response：

You have not included a JOIN condition for the items table and, since the first_name and last_name are dependent on the customer_id then, you can aggregate the name components:

SELECT p.customer_id,
       CASE
       WHEN p.customer_id IS NULL
       THEN NULL
       ELSE MAX(c.first_name)
       END AS first_name,
       CASE
       WHEN p.customer_id IS NULL
       THEN NULL
       ELSE MAX(c.last_name)
       END AS last_name,
       to_char(p.purchase_date, 'YYYY/IW') AS week,
       sum(p.quantity * i.price) total_amt 
FROM   purchases p
       INNER JOIN customers c
       ON p.customer_id = c.customer_id
       INNER JOIN items i
       ON p.product_id = i.product_id
GROUP BY
       ROLLUP(
         p.customer_id,
         to_char(p.purchase_date, 'YYYY/IW')
       ); 

Outputs:

and reversing the ROLLUP:

SELECT p.customer_id,
       CASE
       WHEN p.customer_id IS NULL
       THEN NULL
       ELSE MAX(c.first_name)
       END AS first_name,
       CASE
       WHEN p.customer_id IS NULL
       THEN NULL
       ELSE MAX(c.last_name)
       END AS last_name,
       to_char(p.purchase_date, 'YYYY/IW') AS week,
       sum(p.quantity * i.price) total_amt 
FROM   purchases p
       INNER JOIN customers c
       ON p.customer_id = c.customer_id
       INNER JOIN items i
       ON p.product_id = i.product_id
GROUP BY
       ROLLUP(
         to_char(p.purchase_date, 'YYYY/IW'),
         p.customer_id
       ); 

Outputs:

fiddle