how to calculate average price of each item in inventory php mysql

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.

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

But if we transfer 8 bottles from 20 bottles

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.

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:

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:

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:

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

---