Create new column storing average of rows from another column in MySQL-CodePudding

I am trying to create a new column that has stores the 'Average weight of the field'. For example, the answer for RaceID = 123 would be 54.5. The RaceID's are not organised from smaller to largest and are displayed randomly like the example below.

RaceID	Weight	No. Starters
123	56	2
124	58	2
123	53	2
125	60	2
125	51	2
124	62	2

CodePudding user response：

Try below query, It will display current table data along with average column :

select t.*,
avg(Weight) over(partition by raceID order by raceID ) avg_raceID
from table t;

CodePudding user response：

SELECT RaceID, AVG(Weight) AS val_1
FROM dataset_name
GROUP BY RaceID;

By using above code we can get the average value of weights for every unique RaceID. Check the below image for better understanding.
https://i.stack.imgur.com/kMA68.png
Let me know if there are any modifications or error.