Leaderboard

Sorry, missed the bit about the second select being year and not class. Try SELECT s.id , s.firstname , s.lastname , c.classname FROM class c JOIN student_class sc ON sc.classid = c.id AND semesterid = ? JOIN student s ON sc.studentid = s.id WHERE c.year = ? ORDER BY lastname;

It is high time I tipped my cap to phpfreaks, small but mighty. Phpfreaks may be small in population, but the sense and knowledge derived from it are immeasurable. Here, you find gurus who are always ready to solve our problems. Since I joined this forum, I no longer bother flipping to any other place to get my problems solved because phpfreaks is competent. Please do not relent, and keep up the good work. Regards to you all.

This query uses WINDOW function to get the class averages for each subject. (I am using MariaDB 11.1 but MySql 8 also has them). If you don't have then then us a subquery to get the class averages and join to that. SELECT studentid , subjectname , student_score , ROUND(AVG(student_score) OVER (PARTITION BY subjectname)) as mean FROM ( SELECT sc.semesterid, sc.classid, sc.studentid, s.subjectname, SUM(r.score) AS student_score FROM result r JOIN student_class sc ON r.studentclassid = sc.id JOIN course c ON r.courseid = c.id JOIN subject s ON c.subjectid = s.id JOIN semester sm ON sc.semesterid = sm.id WHERE sm.id = 10 AND sc.classid = 1 GROUP BY subjectname, studentid ) totals ORDER BY studentid, subjectname;