I've got table of
scores where each entry corresponds to a particular
(student, subject) pair.
CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, student_id integer NOT NULL, CONSTRAINT s1_id FOREIGN KEY (subject_id) REFERENCES subject (id), CONSTRAINT s2_id FOREIGN KEY (student_id) REFERENCES student (id), );
I want to pick the top 5 subjects with highest overall scores, and then compute the average score of each student across those 5 subjects. Some students may not have entries for some subjects. Those values would be given a default score.
Here's what I have:
SELECT student_id, AVG(COALESCE(score.value, default_value)) FROM ( SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum FROM score JOIN subject on subject.id = score.subject_id WHERE subject.name != 'skip me' GROUP BY score.subject_id ORDER BY score_sum DESC LIMIT 5 ) AS score_sort LEFT JOIN score ON score_sort.subject_id = score.subject_id GROUP BY student_id
The inner query works correctly to select the top 5. But the
LEFT JOIN in outer query does not select the rows where a student does not have a score. What am I doing wrong here ?