-1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Amr Seyam
  • 1
  • 2
  • 3
    My advice to you as a begineer: please post some a sample of input and output data. It's important to be able to communicate a "repro" (repoduction) of your issue. – Nick.Mc Jul 17 '22 at 12:32
  • I'm not sure whether I understand what you mean or not. Do you want to insert data in `Product` table related to data that exist in `Stock` table? – negin motalebi Jul 17 '22 at 12:42
  • @neginmotalebi I want to make the average of data exists in a column in [Stock] table and insert the output of the average in [Product] table – Amr Seyam Jul 17 '22 at 12:55

2 Answers2

0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tymtam
  • 31,798
  • 8
  • 86
  • 126
0

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.

negin motalebi
  • 351
  • 2
  • 15