SQL Display Inventory Updates

Question

I have a table that displays all the updates of the inventory:

╔══════════════╦════════╦══════════╦═══════╗
║   ItemName   ║  Type  ║ Quantity ║ Date  ║
╠══════════════╬════════╬══════════╬═══════╣
║ BottledWater ║ Add    ║       50 ║ 07/03 ║
║ BottledWater ║ Deduct ║       20 ║ 07/03 ║
║ Chips        ║ Add    ║       30 ║ 07/02 ║
║ BottledWater ║ Deduct ║       10 ║ 07/02 ║
║ Chips        ║ Deduct ║       20 ║ 07/01 ║
╚══════════════╩════════╩══════════╩═══════╝

I would like to write a query and return a table that would display the total of an items added and deducted stocks, the table would look something like:

╔══════════════╦═══════╦══════════╗
║   ItemName   ║ Added ║ Deducted ║
╠══════════════╬═══════╬══════════╣
║ BottledWater ║    50 ║       30 ║
║ Chips        ║    30 ║       20 ║
╚══════════════╩═══════╩══════════╝

Any ideas?

Answer 1

Just use conditional aggregation:

select item,
       sum(case when type = 'Add' then quantity else 0 end) as added,
       sum(case when type = 'Deduct' then quantity else 0 end) as deducted
from inventory
group by item;