1

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.

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Fikri Çetin
  • 17
  • 4

1 Answers1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • It works! thank you very much. How about if I want to impute median instead of mean? – Fikri Çetin Apr 13 '22 at 15:10
  • @FikriÇetin there is no built-in function that calculates the median. Check this: https://stackoverflow.com/questions/15763965/how-can-i-calculate-the-median-of-values-in-sqlite to get some idea. – forpas Apr 13 '22 at 15:15