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.
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 |
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.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.