How to split quantities in separate columns and lines

Question

Details

These are my tables and data:

create table orders (
  id int not null,
  item varchar(10),
  quantity int
 );
 
 insert into orders (id, item, quantity) values
 (1, 'Item 1', 10);
 
 create table orders_picked (
  id int not null,
  orderId int,
  quantity int
 );
 
  insert into orders_picked (id, orderId, quantity) values
 (1, 1, 4),
 (2, 1, 1);

To get a count of picked Items, I run this query:

select item, sum(op.quantity) as quantity from orders o left join orders_picked op on o.id = op.orderId group by item

And this is the output:

Question

Because the table orders has 5 remaining items to be picked. How can I display the remaining items in separate lines, with an extra column to identify whether it's "picked" or "remaining"? Like so:

Answer 1

Use a CTE to join the tables and to aggregate and then use UNION ALL to get separate rows for picked and remaining:

WITH cte AS (
  SELECT o.item,
         o.quantity,
         TOTAL(op.quantity) AS picked_quantity  
  FROM orders o LEFT JOIN orders_picked op 
  ON op.orderId = o.id 
)  
SELECT item, picked_quantity AS quantity, 'Picked' AS Type
FROM cte
UNION ALL
SELECT item, quantity - picked_quantity, 'Remaining'
FROM cte;

See the demo.