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 ?