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:
CodePudding user response:
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.