CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES 

  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 

  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 

  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 

  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT name, test, score,

  AVG(score) OVER (PARTITION BY test) AS average_by_test

FROM student;