SQLite impute missing values by mean for every group-CodePudding

I have an SQLite table as shown below.

students	grades
Nick	34
Nick	42
Nick	86
Nick	Null
John	38
John	12
John	74
John	Null
Colin	87
Colin	23
Colin	46
Colin	42

What I want to do is impute Null values with the mean of each student's grades. For example, missing value for Nick will be 54 and for John 41.3. How can I do this in SQL code? I am using SQLite.

Any help is much appreciated.

CodePudding user response：

Use a correlated subquery in the UPDATE statement:

UPDATE tablename AS t1
SET grades = (
  SELECT ROUND(AVG(t2.grades), 1) 
  FROM tablename AS t2 
  WHERE t2.students = t1.students
)
WHERE t1.grades IS NULL;

See the demo.