[thelist] Calculating weighted averages
Bill Moseley
moseley at hank.org
Sat Jul 14 10:26:14 CDT 2007
FWIW, I'm using Postgresql.
A teacher wants a list of their students and their average grades.
So I have these tables:
create table student (
id primary key,
name text
);
-- Table to track exams and their weights
create table exam (
id primary key,
name text,
weight integer
);
-- Relate students to grades for a specific exam
-- NULL score indicates test not completed (yet).
create table test (
student integer references student(id),
exam integer references exam(id),
score integer -- 0 .. 100, for example
);
So, this seem correct for the weighted average?
SELECT
student.id AS Student,
ROUND(
SUM( ( exam.weight * test.score ) ) / SUM( exam.weight )
) AS Score
FROM
student
LEFT JOIN test on test.student = student.id
LEFT JOIN exam on test.exam = exam.id
GROUP BY
student.id
) AS sum_table;
Left join as I want to display every student even if they were never
given any tests.
Now, here's my real question:
If a student was given a test (a row was added to the
"test" table) but the student has not completed the test yet their
score is NULL. In that case I want the report to look like:
Student | Score
--------+----------
1 | 78
2 | 84
3 | incomplete
4 | 92
That is, if there's a NULL (or some other indicator that the test was
not complete) in any row of the test table for a student I want to
make the aggregate report "incomplete" instead of the average of
the other non-NULL tests.
But, I'm not seeing how to accomplish that.
Thanks,
--
Bill Moseley
moseley at hank.org
More information about the thelist
mailing list