Average column values and show the output in another column-CodePudding

I have some products in a table tbProduct which has these columns:

Pcode
product
Avg_costprice

And I have another table which its name is tbStockIn and it has these columns:

Pcode
Product
Cost-price

How can I take the average of the column Cost-price according to Pcode, and show the output in the column Avg_costprice?

Please help me.

CodePudding user response：

If you want to update Avg_costprice of tbProduct related to tbStockIn use this code:

UPDATE tbProduct SET 
Avg_costprice = (SELECT AVG(Cost-price) FROM tbStockIn
WHERE tbStockIn.Pcode = tbProduct.Pcode)

I hope it helps you with your purpose.

CodePudding user response：

I can see 3 options.

A. Don't have tbProduct

It seems like you may not need tbProduct and you could just ask for Avg_costprice when you need it:

SELECT 
    Pcode, 
    AVG(Cost-price) AS Avg_costprice
FROM tbStockIn;

B. Update tbProduct every time tbStockIn changes

If you have to have tbProduct then every time you interact with tbStockIn you'll need to update the average price. Upon a change to tbStockIn you can:

update the avg. prices only for the products you modify,
recalculate the avg-prices for the whole product table.

C. Computed column

Please see formula for computed column based on different table's column.