Items purchased and not purchased

Question

I have the following setup where 2 items (105, 106) have not been purchased. I want to modify the query to also include those 2 items which haven't been purchased. They should appear as the last 2 rows. I thought a left JOIN may have worked but I was unsuccessful. Any help would be appreciated

I have the following setup where 2 items have not been purchased (105, 106).

How can I modify the last query to show those 2 items with zero purchases? Since there are no purchases they should appear last as I sorting by descending QUANTITY.

If there is a better way to rewrite this query I am very open to any ideas.

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, 'John', 'Doe' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Presto 6-quart Pressure Cooker', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Cuisinart 8-quart Pressure Cooker', 111.99 FROM DUAL UNION ALL
SELECT 102, 'Farberware 3-quart Pressure Cooker', 49.99 FROM DUAL UNION ALL
SELECT 103, 'Farberware 6-quart Pressure Cooker', 89.29 FROM DUAL UNION ALL
SELECT 104, 'Farberware 8-quart Pressure Cooker', 105.99 FROM DUAL UNION ALL 
SELECT 105, 'Breville Fast Slow Pro Pressure Cooker 3 quart', 39.95 FROM DUAL UNION ALL 
SELECT 106, 'Breville Fast Slow Pro Pressure Cooker 6 quart', 59.95 FROM DUAL;


create table purchases(
  ORDER_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, 99+LEVEL, 
2, TIMESTAMP '2024-04-03 05:18:03' + numtodsinterval ( (LEVEL -1) * 1, 'day' ) + numtodsinterval ( LEVEL * 37, 'minute' ) +  numtodsinterval ( LEVEL * 3, 'second' ) FROM    dual CONNECT BY  LEVEL <= 5;


/* items never purchased */

select * from items i
where not exists
(select 1 from purchases p where p.product_id = i.PRODUCT_ID)

PRODUCT_ID PRODUCT_NAME PRICE
105 Breville Fast Slow Pro Pressure Cooker 3 quart 39.95
106 Breville Fast Slow Pro Pressure Cooker 6 quart
59.95



/* Total quantity purchased each item */

with prep (product_id, tot_quantity) as 
(
   select 
      product_id, 
      sum(quantity)
    from   purchases
    group  by product_id
  )
SELECT  
     p.product_id, 
     I.product_name,
  tot_quantity,
     sum(tot_quantity * i.PRICE) 
"TOTAL_AMT"
 FROM prep p
JOIN items i ON i.product_id = p.product_id  
GROUP BY  p.product_id, i.product_name, tot_quantity 
ORDER BY tot_quantity desc,
product_id;

Answer 1

You need to start with the items table and outer join to purchases. Any non-matched rows will return NULL for columns from purchases, which you can translate to a 0 quantity, which you are already ordering by in descending order so they would be last.

ANSI:

SELECT i.product_id,
       i.product_name,
       SUM(NVL(p.quantity,0)) tot_quantity,
       SUM(NVL(p.quantity,0) * i.price) total_amt
  FROM items i
       LEFT OUTER JOIN purchases p ON p.product_id = i.product_id
 GROUP BY i.product_id,
          i.product_name
 ORDER BY tot_quantity DESC

Native Oracle:

SELECT i.product_id,
       i.product_name,
       SUM(NVL(p.quantity,0)) tot_quantity,
       SUM(NVL(p.quantity,0) * i.price) total_amt
  FROM items i,
       purchases p 
 WHERE i.product_id = p.product_id(+)
 GROUP BY i.product_id,
          i.product_name
 ORDER BY tot_quantity DESC