skip to Main Content

I have created a warehouse inventory report page. I’m trying to calculate average price of every item present in our warehouse. The items have different price and quantity every time we purchase. For eg. we purchased 10 bottles of water for $2 each but next time we might purchase 20 bottles for $3 each.

Quantity Unit Price Total
10 2 20
20 3 60

The average for the bottles will be 80 bottles / 30 bottles = $2.66

But if we transfer 8 bottles from 20 bottles

Quantity Unit Price Total
10 2 20
12 3 36

Now the average for the bottles will be 56 bottles / 22 bottles = $2.54

My query to get this is

SELECT 
`inventory`.`name` AS `item_name`, 
`items`.`brand`,
ROUND (AVG(stockreceive_trans.price), 2) AS price,
`categories`.`name` AS `category_name`,
items.measure_unit,
inventory.barcode,
inv_quantity
FROM `inventory`
JOIN `items` ON inventory.barcode = items.barcode            
JOIN `categories` ON inventory.category_id = categories.id
JOIN `stockreceive_trans` ON items.id = stockreceive_trans.item_id
WHERE inventory.warehouse_id = '$warehouse_id'
GROUP BY items.id

but I cannot figure out how to get this right. Any help is much appreciated. Thanks in advance.

2

Answers


  1. In you question incomplete, as don’t share the other table information.
    According to your currect table:

    Quantity Unit Price Total
    10 2 20
    20 3 60

    inforation solution is:

    
    CREATE TABLE T (quantity int, unit_price int, total int);
    
    INSERT INTO T (quantity , unit_price , total ) VALUES 
        ( 10, 2, 20 ),
        ( 20, 3, 60 );
    
    -- ==============
    
    select 
      round(sum(total)/sum(quantity), 2) as avg_bottles_price,
      round(sum(quantity*unit_price)/sum(quantity), 2) as avg_bottles_price_alternative
    from T;
    

    I hope issue will resolve your end.

    Thank you !!!

    Login or Signup to reply.
  2. From the table definitions you created in DBFiddle I can see there are problems with the design which you really should fix, but we can make your query work.

    The current structure is as follows:

    original schema

    I’ve shown the relationship of items and inventory using the item_id but in your query you are using barcode, either should work but since you have a foreign key we should use it and storing the barcode in both tables means the design is not normalised.

    Without using any window functions you can group and calculate the sum/avg of each product using a nested query. I’ve used the least number of fields from the tables to show it working, you can add any extra columns you need.

    I have not joined items to inventory as all the information you need to calculate the avg price is available in the stockreceive_trans table.

    select 
        item_name
        ,sum(quantity) total_quantity
        ,sum(total_cost) total_cost
        ,ROUND(sum(total_cost)/sum(quantity),2) avg_price
    from (
      select 
          item_name 
          ,stockreceive_trans.price
          ,quantity
          ,round(stockreceive_trans.price*quantity, 2) total_cost
      from 
          items
          join stockreceive_trans on items.id = stockreceive_trans.item_id
    ) stock
    group by item_name;
    

    See the updated DBFiddle for a working query.

    The result of this query is:

    item_name total_quantity total_cost avg_price
    Bottled Water 30.000 80.00 2.67

    As you have not described how sales are recorded, changing the quantity inserted into the stockreceive_trans will suffice.

    INSERT INTO `stockreceive_trans`(
        `id`, `reference_id`, `item_id`, `item_name`,
        `measure_unit`, `barcode`, `warehouse_id`,
        `category_id`, `sub_category`, `quantity`, `price`)
    VALUES (2,1,1,'Bottled Water','bottle','62eba14d9e142',1,1,0,20,3);
    --                                                           ^^
    --                                        change quantity to 12
    

    Running the query again give the result:

    item_name total_quantity total_cost avg_price
    Bottled Water 22.000 56.00 2.55

    Issues with the database design

    • The name of the table stockreceive_trans implies it’s only for incoming stock, where are you recording sold, or outgoing, stock? If you are using a separate table it would be better to combine them.
    • The inventory table is not needed if the stock_trans table were to record all incoming and outgoing stock movements. A sum of the quantity column (adding positive and negative movements) would give you the current inventory. Storing a total in another table mean at some point the totals will not match.
    • The tables are not Normalised

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search