I am a novice and in my place of work, I am directed to change the output of the result attached with this my writeup.
We usually use CA1 as our Midterm result, but this time the management said they want to be using the CA1 and CA2 as the Midterm which affect the previous method. The total mark for each CA is 10 but during the midterm we do multiply it by 10 to make it 100 for the grades.
Which is written in the SQL query score*10 as ca. But now when I am asked to add the CA2, it means the total mark for CA1 and CA2 should not exceed 100 because the grade is in percentage and should not exceed 100.
I don’t know how to go about this, I have done some try by error but all to no avail. I would be glad if I could get a help on this. Here is the sql statement
, score*10 as ca
FROM result r
JOIN
(
student_class sc
JOIN class cl ON sc.classid = cl.id
JOIN level l ON cl.levelid = l.id
JOIN course c ON c.levelid = l.id
JOIN student st ON sc.studentid = st.id
JOIN semester sm ON sc.semesterid = sm.id
JOIN session sn ON sm.sessionid = sn.id
JOIN subject s ON c.subjectid = s.id
) ON r.studentclassid = sc.id AND exam = 'CA1' and r.courseid = c.id
From the sql above, on the last line, I don’t know how to include the CA2, I have tried like separating with comma but not working and on the first line above, can I declare score5 as ca1 and score5 as ca2?
