[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?

        student.id AS Student,
            SUM( ( exam.weight * test.score ) ) / SUM( exam.weight )
        ) AS Score
        LEFT JOIN test on test.student = student.id
        LEFT JOIN exam on test.exam = exam.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.


Bill Moseley
moseley at hank.org

More information about the thelist mailing list