Complex SQL Query for Inventory System

Question

I have 5 tables:

1. Items
   

2. Inventory
   

3. ConsumedItemsMonitoring
   

4. DamagedItemsMonitoring
   

5. UnaccountedItems
   

I'm new to Complex SQL queries did some research and asked for help and this is what I my code looks like so far.

SELECT Items.ItemID, Items.Item, 
SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) < CURDATE() THEN Inventory.Quantity ELSE 0 END) - 
SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) <CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 end) - 
SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate)<CURDATE() THEN damagedinventory.Quantity ELSE 0 end) - 
SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate)<CURDATE() THEN unaccounteditems.Quantity ELSE 0 end) AS 'PrevBalance',

SUM(CASE WHEN DATE(Inventory.ItemTransactionDate)=CURDATE() THEN Inventory.Quantity else 0 END) AS 'DeliveredToday',

SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate)=CURDATE() THEN damagedinventory.Quantity ELSE 0 END) AS 'DamagedToday',

SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate)=CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END) AS 'ConsumedToday',

SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate)=CURDATE() THEN unaccounteditems.Quantity ELSE 0 END) AS 'UnAccountedToday',

SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) < CURDATE() THEN Inventory.Quantity else 0 end)-
SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) < CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END)-
SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) < CURDATE() THEN damagedinventory.Quantity ELSE 0 END)-
SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate) < CURDATE() THEN unaccounteditems.Quantity ELSE 0 END)-
SUM(CASE WHEN DATE(consumeditemmonitoring.TransactionDate) = CURDATE() THEN consumeditemmonitoring.Quantity ELSE 0 END)-
SUM(CASE WHEN DATE(damagedinventory.ItemTransactionDate) = CURDATE() THEN damagedinventory.Quantity ELSE 0 END)-
SUM(CASE WHEN DATE(unaccounteditems.ItemTransactionDate) = CURDATE() THEN unaccounteditems.Quantity ELSE 0 END) +
SUM(CASE WHEN DATE(Inventory.ItemTransactionDate) = CURDATE() then Inventory.Quantity ELSE 0 end) AS 'Total Balance' 

FROM Items

LEFT OUTER JOIN consumeditemmonitoring ON consumeditemmonitoring.ItemID = Items.ItemID 
LEFT OUTER JOIN damagedinventory ON damagedinventory.ItemID = Items.ItemID
LEFT OUTER JOIN unaccounteditems ON unaccounteditems.ItemID = Items.ItemID
LEFT OUTER JOIN inventory ON inventory.ItemID= Items.ItemID
GROUP BY Items.ItemID

The output looks like some of the table are multiplied.

Answer 1

What you are seeing is a result of how joins work and the fact that the joins are executed before the group by. I can illustrate this with a simplified version of your data.

drop table if exists
items,
items_inventory,
items_consumed,
items_damaged,
items_unaccounted;

create table items (id int);
create table items_inventory(id int,itemid int,qty int);
create table items_consumed(id int,itemid int,qty int);
create table items_damaged(id int,itemid int,qty int);
create table items_unaccounted(id int,itemid int,qty int);

insert into items values(1),(2);
insert into items_inventory values (1,1,10),(2,1,10),(2,2,20);
insert into items_consumed values(1,1,5),(2,2,15);
insert into items_damaged values(1,1,25);

If we run a simple select

select i.id,
         ii.id,ii.qty,
         ic.id,ic.qty,
         id.id,id.qty,
         iu.id,iu.qty
from items i
left join items_inventory   ii on ii.itemid = i.id
left join items_consumed    ic on ic.itemid = i.id
left join items_damaged     id on id.itemid = i.id
left join items_unaccounted iu on iu.itemid = i.id
;

we get 2 rows for item 1 even though there is only 1 row for items_consumed

+------+------+------+------+------+------+------+------+------+
| id   | id   | qty  | id   | qty  | id   | qty  | id   | qty  |
+------+------+------+------+------+------+------+------+------+
|    1 |    1 |   10 |    1 |    5 |    1 |   25 | NULL | NULL |
|    1 |    2 |   10 |    1 |    5 |    1 |   25 | NULL | NULL |
|    2 |    2 |   20 |    2 |   15 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

When we aggregate

select i.id,
         count(*) as rows,
         sum(ii.qty) as inventory,
         sum(ic.qty) as consumed,
         sum(id.qty) as damaged,
         sum(iu.qty) as unaccounted
from items i
left join items_inventory   ii on ii.itemid = i.id
left join items_consumed    ic on ic.itemid = i.id
left join items_damaged     id on id.itemid = i.id
left join items_unaccounted iu on iu.itemid = i.id
group by i.id;

we get 'doubling' up of consumed and damaged.

+------+------+-----------+----------+---------+-------------+
| id   | rows | inventory | consumed | damaged | unaccounted |
+------+------+-----------+----------+---------+-------------+
|    1 |    2 |        20 |       10 |      50 |        NULL |
|    2 |    1 |        20 |       15 |    NULL |        NULL |
+------+------+-----------+----------+---------+-------------+
2 rows in set (0.00 sec)

One way to deal with this is to aggregate BEFORE you join by pushing the aggregations into sub queries which you would then join. For example

select i.id, ii.inventory,ic.consumed,id.damaged,iu.unaccounted,
            coalesce(ii.inventory,0)+coalesce(ic.consumed,0)+coalesce(id.damaged,0)+coalesce(iu.unaccounted,0) total
from items i
left join (select ii.itemid,sum(ii.qty) as inventory   from items_inventory ii group by itemid)   ii on ii.itemid = i.id
left join (select ic.itemid,sum(ic.qty) as consumed    from items_consumed  ic group by itemid)   ic on ic.itemid = i.id
left join (select id.itemid,sum(id.qty) as damaged     from items_damaged   id group by itemid)   id on id.itemid = i.id
left join (select iu.itemid,sum(iu.qty) as unaccounted from items_unaccounted iu group by itemid) iu on iu.itemid = i.id
;

+------+-----------+----------+---------+-------------+-------+
| id   | inventory | consumed | damaged | unaccounted | total |
+------+-----------+----------+---------+-------------+-------+
|    1 |        20 |        5 |      25 |        NULL |    50 |
|    2 |        20 |       15 |    NULL |        NULL |    35 |
+------+-----------+----------+---------+-------------+-------+
2 rows in set (0.00 sec)