This is my statement for the final course grade for all those students taking MATH, how can I adjust this so it displays the final course grade for MATH, but only for students who are taking both MATH and ART ?
SELECT sum(mark.mark * assignment.weighting/100) FROM mark, assignment WHERE (assignment.course = 'MATH') and (assignment.course = mark.course) and (assignment.assignnum = mark.assignnum) group by mark.studnum
Advertisement
Answer
You can do it with conditional aggregation:
SELECT m.studnum, SUM(CASE WHEN m.course = 'MATH' THEN m.mark * a.weighting / 100 END) final_course_grade FROM mark m INNER JOIN assignment a ON a.course = m.course AND a.assignnum = m.assignnum WHERE m.course IN ('MATH', 'ART') GROUP BY m.studnum HAVING COUNT(DISTINCT m.course) = 2
First, you should use a proper join with an ON
clause and not that archaic syntax with the ,
between the table names and the join conditions in the WHERE
clause.
For your requirement you must remove the condition assignment.course = 'MATH'
from the WHERE
clause because you want to check that the students took 'ART'
also.
This is done in the HAVING
clause.
Also, note that if both mark
and weighting
are integers, then in this expression:
m.mark * a.weighting / 100
the division will truncate any decimal part because SQLite performs integer division between integers.
So, if you want a result with decimal part you should divide by 100.0
:
m.mark * a.weighting / 100.0