1

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:

enter image description here

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:

enter image description here

forpas
  • 160,666
  • 10
  • 38
  • 76

1 Answers1

0

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • What if it's over picked? Item remaining shouldn't be -4 –  Jan 18 '23 at 15:53
  • 1
    @Eduards depending on your requirement you might use `max(quantity - picked_quantity, 0)` instead of `quantity - picked_quantity` or a WHERE clause in the 2nd unioned query: `WHERE quantity - picked_quantity >= 0` – forpas Jan 18 '23 at 16:04