Keeping a Running Total of Inventory Quantity in MySQL Database Query/Operation for same SKU/entries

Question

Here is my table ws_sold I'm doing testing with

Along with my ws_inventory table

My query is as followed:

SELECT
  inventory.id,
  inventory.sku AS inventory_sku,
  inventory.quantity AS inventory_quantity,
  mastersku.sku2,
  mastersku.sku1,
  mastersku.sku3,
  mastersku.multsku,
  mastersku.qtysku,
  mastersku.altsku,
  mastersku.sku,
  sold.quantity AS sold_quantity,
  sold.sku AS sold_sku
FROM sold
  LEFT OUTER JOIN mastersku
    ON sold.sku = mastersku.sku
  LEFT OUTER JOIN inventory
    ON mastersku.sku1 = inventory.sku
    OR mastersku.altsku = inventory.sku

Which has an output of:

Everything is great, besides the inventory_quantity column results.

My query is not taking into consideration previous equations in earlier rows for same SKU entries, and is assuming each query is starting fresh from the ws_inventory Quantity of 99.

The logic in this is (I've done so with both PHP and MySQL in testing as I'm open to both):

inventory_quantity - (sold_quantity * ws_mastersku.QtySKU)

Therefore the first result for WS16 is 99 - (2 * 4) = 91.

This is correct.

But, the second instance of WS16 is 99 - (4 * 4) = 83.

And is therefore over-writing the first result.

I'm looking for a query that will keep the running total on inventory_quantity if (such as in this test case), there are more than one of the same SKU being processed.

Something such as this:

1   WS16    91  (null)  (null)  (null)  (null)  0   4   WS16    WS16X4-2    WS16X4-2    2
2   WS3     97  (null)  (null)  (null)  (null)  0   2   WS3      WS3X2-4      WS3X2-4   1
3   WS6     95  (null)  (null)  (null)  (null)  0   4   WS6     WS6X4-16    WS6X4-16    1
4   WS16    75  (null)  (null)  (null)  (null)  0   4   WS16    WS16X4-2    WS16X4-2    4

I realize this issue is arrising because inventory_quantity is taken at the start the query as its initial number, and is not updating based off processes later down in the line.

Any suggestions/help please? It has taken me a while just to get to this point in the project, being rather new to MySQL it has been a big learning experience all the way through, but this issue is causing a huge barrier for me.

Thank you!

Answer 1

You should reconsider your database design and the questions you need to answer. You are using the ws_inventory.quantity field to store the initial quantity when in fact it should be showing the current available quantity. This will instantly show the answer to the question: "how many widgets do I have left?"

You should be decrementing the inventory in ws_inventory as you sell each unit. You can do this with a trigger in MySQL when you add a quantity to the ws_sold table you update the ws_inventory.quantity field. You may also want to do this as a separate query in your application (PHP?) code. The quantity should show quantity on hand: that way if you have 99 and sell 2 and then sell 3 more your ws_inventory.quantity field should be 94 (94 = 99 - 2 - 3). You can also add to this field when you replenish inventory.

This is how the trigger should work: (from Update another table after insert using a trigger?)

-- run this SQL code on your database
-- note that NEW.quantity and NEW.sku are what you inserted into ws_sold
CREATE TRIGGER update_quantity
AFTER INSERT ON ws_sold
FOR EACH ROW
  UPDATE ws_inventory
     SET ws_inventory.quantity = ws_inventory.quantity - NEW.quantity
   WHERE ws_inventory.sku = NEW.sku;

If you need to maintain a history of inventory for reports like the one above, then you may want to consider an ws_inventory_history table that can take snapshots.

Answer 2

The following answer works by joining the sold items with the last sold items after grouping them on inventory.id and by resetting the variables when the inventory.id changes. Note how the join is on the inventory.id and group_row - 1 = group_row

This SQL example works

select sold.inventory_id 
, sold.sold_id
, case sold.inventory_id when @inventoryId then
        @starting := @starting 
    else
        @starting := sold.inventory_quantity_before_sale    
  end as starting_quantity
, case sold.inventory_id when @inventoryId then
        @runningSold := @runningSold + coalesce(last.quantity_sold,0) 
    else
        @runningSold := 0   
  end as running_sold
, case sold.inventory_id when @inventoryId then
        @runningInventoryQuantity := @starting - @runningSold
    else
        @runningInventoryQuantity := sold.inventory_quantity_before_sale
  end as before_sale
, sold.quantity_sold
, sold.inventory_quantity_before_sale - sold.quantity_sold - @runningSold after_sale
, @inventoryId := sold.inventory_id group_inventory_id -- clocks over the group counter
from (
    select inventorySold.*
    , case inventory_id
        when @inventoryId then
            @groupRow := @groupRow + 1
        else
            @groupRow := 1
        end as group_row
    ,  @inventoryId := inventory_id as group_inventory_id
    from (
     SELECT
       inventory.id inventory_id
    ,  inventory.quantity AS inventory_quantity_before_sale
    ,  mastersku.qtysku * sold.quantity quantity_sold
    ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
    FROM ws_sold sold
      LEFT OUTER JOIN ws_mastersku mastersku
        ON sold.sku = mastersku.sku
      LEFT OUTER JOIN ws_inventory inventory
        ON mastersku.sku1 = inventory.sku
        OR mastersku.altsku = inventory.sku
    ) inventorySold
    join ( select @groupRow := 0, @inventoryId := 0 ) variables
    order by inventory_id, sold_id
) sold
left join (
    select inventorySold.*
    , case inventory_id
        when @inventoryId then
            @groupRow := @groupRow + 1
        else
            @groupRow := 1
        end as group_row
    ,  @inventoryId := inventory_id as group_inventory_id
    from (
     SELECT
       inventory.id inventory_id
    ,  inventory.quantity AS inventory_quantity
    ,  mastersku.qtysku * sold.quantity quantity_sold
    ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
    FROM ws_sold sold
      LEFT OUTER JOIN ws_mastersku mastersku
        ON sold.sku = mastersku.sku
      LEFT OUTER JOIN ws_inventory inventory
        ON mastersku.sku1 = inventory.sku
        OR mastersku.altsku = inventory.sku
    ) inventorySold
    join ( select @groupRow := 0, @inventoryId := 0 ) variables
    order by inventory_id, sold_id
) `last`
on sold.inventory_id = `last`.inventory_id
and sold.group_row - 1 = `last`.group_row
join ( select @runningInventoryQuantity := 0, @runningSold := 0, @inventoryId := 0, @afterSold := 0, @starting :=0 ) variables
order by sold.inventory_id, sold.group_row

-- example results

inventory_id    sold_id starting_quantity   running_sold    before_sale quantity_sold   after_sale  group_inventory_id
1   1   93  0   93  4   89  1
1   4   93  4   89  16  73  1
1   5   93  20  73  20  53  1
1   12  93  40  53  48  5   1
2   2   97  0   97  4   93  2
2   6   97  4   93  12  81  2
2   7   97  16  81  14  67  2
2   8   97  30  67  16  51  2
2   11  97  46  51  22  29  2
3   3   95  0   95  12  83  3
3   9   95  12  83  36  47  3
3   10  95  48  47  40  7   3

You could do the same thing in PHP. Have a starting quantity, a running amount sold and a running total that gets reset each time the inventory id changes and then use the quantity sold for each transaction to adjust those variables.

If you choose the PHP route example sql is

select sold.* from (
    select inventorySold.*
    , case inventory_id
        when @inventoryId then
            @groupRow := @groupRow + 1
        else
            @groupRow := 1
        end as group_row
    ,  @inventoryId := inventory_id as group_inventory_id
    from (
     SELECT
       inventory.id inventory_id
    ,  inventory.quantity AS inventory_quantity_before_sale
    ,  mastersku.qtysku * sold.quantity quantity_sold
    ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
    FROM ws_sold sold
      LEFT OUTER JOIN ws_mastersku mastersku
        ON sold.sku = mastersku.sku
      LEFT OUTER JOIN ws_inventory inventory
        ON mastersku.sku1 = inventory.sku
        OR mastersku.altsku = inventory.sku
    ) inventorySold
    join ( select @groupRow := 0, @inventoryId := 0 ) variables
    order by inventory_id, sold_id
) sold

-- example results

inventory_id    inventory_quantity_before_sale  quantity_sold   sold_id group_row   group_inventory_id
1   93  4   1   1   1
1   93  16  4   2   1
1   93  20  5   3   1
1   93  48  12  4   1
2   97  4   2   1   2
2   97  12  6   2   2
2   97  14  7   3   2
2   97  16  8   4   2
2   97  22  11  5   2
3   95  12  3   1   3
3   95  36  9   2   3
3   95  40  10  3   3

You can get the label information by joining other tables to the result using inventory.id and sold.id.

I agree with https://stackoverflow.com/users/932820/chris-adams. If you're looking to keep a track of stocktakes over time then you'll need a transaction table to record the starting and ending inventory quantities and starting and ending timestamps ... and probably starting and ending sold ids.

-- supporting tables - run this in an empty database unless you want to destroy your current tables

DROP TABLE IF EXISTS `ws_inventory`;

CREATE TABLE `ws_inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(20) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `ws_inventory` WRITE;
/*!40000 ALTER TABLE `ws_inventory` DISABLE KEYS */;

INSERT INTO `ws_inventory` (`id`, `sku`, `quantity`)
VALUES
    (1,'WS16',93),
    (2,'WS3',97),
    (3,'WS6',95);

/*!40000 ALTER TABLE `ws_inventory` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table ws_mastersku
# ------------------------------------------------------------

DROP TABLE IF EXISTS `ws_mastersku`;

CREATE TABLE `ws_mastersku` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(20) DEFAULT NULL,
  `sku1` varchar(20) DEFAULT NULL,
  `sku2` varchar(20) DEFAULT NULL,
  `sku3` varchar(20) DEFAULT NULL,
  `multsku` tinyint(2) DEFAULT NULL,
  `qtysku` int(11) DEFAULT NULL,
  `altsku` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `ws_mastersku` WRITE;
/*!40000 ALTER TABLE `ws_mastersku` DISABLE KEYS */;

INSERT INTO `ws_mastersku` (`id`, `sku`, `sku1`, `sku2`, `sku3`, `multsku`, `qtysku`, `altsku`)
VALUES
    (1,'WS16X4-2',NULL,NULL,NULL,NULL,4,'WS16'),
    (2,'WS3X2-4',NULL,NULL,NULL,NULL,2,'WS3'),
    (3,'WS6X4-16',NULL,NULL,NULL,NULL,4,'WS6');

/*!40000 ALTER TABLE `ws_mastersku` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table ws_sold
# ------------------------------------------------------------

DROP TABLE IF EXISTS `ws_sold`;

CREATE TABLE `ws_sold` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(20) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `ws_sold` WRITE;
/*!40000 ALTER TABLE `ws_sold` DISABLE KEYS */;

INSERT INTO `ws_sold` (`id`, `sku`, `quantity`)
VALUES
    (1,'WS16X4-2',1),
    (2,'WS3X2-4',2),
    (3,'WS6X4-16',3),
    (4,'WS16X4-2',4),
    (5,'WS16X4-2',5),
    (6,'WS3X2-4',6),
    (7,'WS3X2-4',7),
    (8,'WS3X2-4',8),
    (9,'WS6X4-16',9),
    (10,'WS6X4-16',10),
    (11,'WS3X2-4',11),
    (12,'WS16X4-2',12);

/*!40000 ALTER TABLE `ws_sold` ENABLE KEYS */;
UNLOCK TABLES;

Answer 3

Using the schema you supplied.

The query for SQL.

select sold.inventory_id 
    , sold.sold_id
    , case sold.inventory_id when @inventoryId then
            @starting := @starting 
        else
            @starting := sold.inventory_quantity_before_sale    
      end as starting_quantity
    , case sold.inventory_id when @inventoryId then
            @runningSold := @runningSold + coalesce(last.quantity_sold,0) 
        else
            @runningSold := 0   
      end as running_sold
    , case sold.inventory_id when @inventoryId then
            @runningInventoryQuantity := @starting - @runningSold
        else
            @runningInventoryQuantity := sold.inventory_quantity_before_sale
      end as before_sale
    , sold.quantity_sold
    , sold.inventory_quantity_before_sale - sold.quantity_sold - @runningSold after_sale
    , @inventoryId := sold.inventory_id group_inventory_id -- clocks over the group counter
    from (
        select inventorySold.*
        , case inventory_id
            when @inventoryId then
                @groupRow := @groupRow + 1
            else
                @groupRow := 1
            end as group_row
        ,  @inventoryId := inventory_id as group_inventory_id
        from (
         SELECT
           inventory.id inventory_id
        ,  inventory.quantity AS inventory_quantity_before_sale
        ,  mastersku.qtysku * sold.quantity quantity_sold
        ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
        FROM ws_sold sold
          LEFT OUTER JOIN ws_mastersku mastersku
            ON sold.sku = mastersku.sku
          LEFT OUTER JOIN ws_inventory inventory
            ON mastersku.sku_1 = inventory.sku
            OR mastersku.altsku = inventory.sku
        ) inventorySold
        join ( select @groupRow := 0, @inventoryId := 0 ) variables
        order by inventory_id, sold_id
    ) sold
    left join (
        select inventorySold.*
        , case inventory_id
            when @inventoryId then
                @groupRow := @groupRow + 1
            else
                @groupRow := 1
            end as group_row
        ,  @inventoryId := inventory_id as group_inventory_id
        from (
         SELECT
           inventory.id inventory_id
        ,  inventory.quantity AS inventory_quantity
        ,  mastersku.qtysku * sold.quantity quantity_sold
        ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
        FROM ws_sold sold
          LEFT OUTER JOIN ws_mastersku mastersku
            ON sold.sku = mastersku.sku
          LEFT OUTER JOIN ws_inventory inventory
            ON mastersku.sku_1 = inventory.sku
            OR mastersku.altsku = inventory.sku
        ) inventorySold
        join ( select @groupRow := 0, @inventoryId := 0 ) variables
        order by inventory_id, sold_id
    ) `last`
    on sold.inventory_id = `last`.inventory_id
    and sold.group_row - 1 = `last`.group_row
    join ( select @runningInventoryQuantity := 0, @runningSold := 0, @inventoryId := 0, @afterSold := 0, @starting :=0 ) variables
    order by sold.inventory_id, sold.group_row

-- example results

inventory_id    sold_id starting_quantity   running_sold    before_sale quantity_sold   after_sale  group_inventory_id
1   1   99  0   99  8   91  1
1   4   99  8   91  16  75  1
2   2   99  0   99  2   97  2
3   3   99  0   99  4   95  3

The query for PHP.

select sold.* from (
    select inventorySold.*
    , case inventory_id
        when @inventoryId then
            @groupRow := @groupRow + 1
        else
            @groupRow := 1
        end as group_row
    ,  @inventoryId := inventory_id as group_inventory_id
    from (
     SELECT
       inventory.id inventory_id
    ,  inventory.quantity AS inventory_quantity_before_sale
    ,  mastersku.qtysku * sold.quantity quantity_sold
    ,  sold.id as sold_id -- for order ... you'd probably use created timestamp or finalised timestamp
    FROM ws_sold sold
      LEFT OUTER JOIN ws_mastersku mastersku
        ON sold.sku = mastersku.sku
      LEFT OUTER JOIN ws_inventory inventory
        ON mastersku.sku_1 = inventory.sku
        OR mastersku.altsku = inventory.sku
    ) inventorySold
    join ( select @groupRow := 0, @inventoryId := 0 ) variables
    order by inventory_id, sold_id
) sold

-- example results

inventory_id    inventory_quantity_before_sale  quantity_sold   sold_id group_row   group_inventory_id
1   99  8   1   1   1
1   99  16  4   2   1
2   99  2   2   1   2
3   99  4   3   1   3