1

I want to display the remaining quantity in a supply.

I have two tables:

`supply_inventory_list` table:

supply_id quantity unit item_name supply_description date_received --------- -------- ------ ---------------- ----------------------- ------------- 1 5 REAM LEGAL BOND PAPER CANON LEGAL BOND PAPER 2019-08-27 2 5 REAM A4 BOND PAPER PAPER ONE A4 BOND PAPER 2019-08-27 3 5 REAM LEGAL BOND PAPER HP LEGAL BOND PAPER 2019-08-30

`supply_employee_list` table:

emp_supply_id supply_id deployed_quantity employee_name date_deployed ------------- --------- ----------------- ----------------------- -------------------- 1 3 2 ALEX WARD 2019-08-29 16:00:00 2 3 1 EDWARD COLLINS 2019-08-29 16:00:00

This is the query I tried, but I am not getting the result that i want:

SELECT supply_inventory_list.supply_id, quantity, quantity - IFNULL(deployed_quantity, 0) AS 'AVAILABLE QUANTITY', unit, item_name, supply_description, date_received, IFNULL(deployed_quantity, 0) AS 'deployed_quantity' FROM supply_inventory_list LEFT JOIN supply_employee_list ON supply_employee_list.`supply_id` = supply_inventory_list.`supply_id` WHERE item_name = 'LEGAL BOND PAPER' ORDER BY date_received DESC

This is the result I'm getting:

supply_id quantity AVAILABLE QUANTITY unit item_name supply_description date_received deployed_quantity --------- -------- ------------------ ------ ---------------- ---------------------- ------------- ------------------- 3 5 3 REAM LEGAL BOND PAPER HP LEGAL BOND PAPER 2019-08-30 2 3 5 4 REAM LEGAL BOND PAPER HP LEGAL BOND PAPER 2019-08-30 1 1 5 5 REAM LEGAL BOND PAPER CANON LEGAL BOND PAPER 2019-08-27 0

This is not the result I'm looking for. I the `quantity` to be deducted based on `deployed_quantity`. This is the result I'm looking for:

supply_id quantity AVAILABLE QUANTITY unit item_name supply_description date_received deployed_quantity --------- -------- ------------------ ------ ---------------- ---------------------- ------------- ------------------- 3 5 2 REAM LEGAL BOND PAPER HP LEGAL BOND PAPER 2019-08-30 3 1 5 5 REAM LEGAL BOND PAPER CANON LEGAL BOND PAPER 2019-08-27 0

As you can see the `AVAILABLE QUANTITY` is 2 and the `deployed_quantity` is 3 because the first employee deployed 2 quantities and the second one deployed 1.

How do I produce this result?

Improve this question

2 Answers 2

Reset to default
1

You first need to find the sum of deployed quantity then subtract that from inventory quantity -

SELECT SIL.supply_id, SIL.quantity, SIL.quantity - IFNULL(SEL.deployed_quantity, 0) AS 'AVAILABLE QUANTITY', SIL.unit, SIL.item_name, SIL.supply_description, SIL.date_received, IFNULL(SEL.deployed_quantity, 0) AS 'deployed_quantity' FROM supply_inventory_list SIL LEFT JOIN (SELECT supply_id, SUM(deployed_quantity) deployed_quantity FROM supply_employee_list GROUP BY supply_id) SEL ON SEL.`supply_id` = SIL.`supply_id` WHERE SIL.item_name = 'LEGAL BOND PAPER' ORDER BY SIL.date_received DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You should use the aggregated result for `supply_employee_list`: sum(deployed_quantity) group by supply_id instead of the individual rows `from supply_employee_list`:

SELECT i.supply_id, i.quantity, i.quantity - IFNULL(e.deployed_quantity, 0) AS 'AVAILABLE QUANTITY', i.unit, i.item_name, i.supply_description, i.date_received, IFNULL(e.deployed_quantity, 0) AS 'deployed_quantity' FROM supply_inventory_list i LEFT JOIN ( SELECT supply_id, SUM(deployed_quantity) deployed_quantity FROM supply_employee_list GROUP BY supply_id ) e ON e.supply_id = i.supply_id` WHERE i.item_name = 'LEGAL BOND PAPER' ORDER BY date_received DESC;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.