[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