[thelist] Calculating weighted averages

r937 rudy at r937.com
Sat Jul 14 15:13:03 CDT 2007

if a student has not taken a particular exam, there will be no row in the 
test table for that student for that exam

thus, since it's a LEFT OUTER JOIN, all columns from the test table in the 
query will be null for this situation

consequently, computation involving test.score will be null too

but you knew all that   ;o)

the answer to your question is the COALESCE function

note, though, that to show the word "incomplete" you are showing a string, 
consequently you may have to CAST the computation to CHAR or VARCHAR

    ...   COALESCE(CAST(ROUND(...) AS CHAR), 'incomplete') as Score


More information about the thelist mailing list