'SQLite impute missing values by mean for every group

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.

sqlsqlitegroup-bysql-updatecorrelated-subquery


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 forpas

Related Questions