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

CodeLearner
May 1, 2024
64 views
1 vote
2 Answers

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.

Tags: mysql sql

Answers

- ArtBindu
- May 1, 2024 at 10:06 am
- 0 votes
0
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.

- Tony
- May 1, 2024 at 11:06 pm
- 0 votes
0
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:

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.