Hi, I am doing a student progression system where the application is able to keep track of student marks. I have a problem currently and I've been spending nights on this.
Let me briefly explain. Marks are calculated based on their hurdles. So for instance, H1 = xx%, H2 = xx%. The number of hurdles vary for different subjects. The code below is able to provide me the calculated marks based on each individual's hurdles in rows. However, I'm trying to output it in a horizontal format. Meaning:
I want it to be: xx% xx% (in two columns)
I've tried using the CASE clause in my SELECT section but that only works if the hurdles are static and fixed. My problem is the hurdles are dynamic as well. Is there anyone who has some idea on how to workaround this issue? Thanks in advance guys!! :)
SELECT SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total FROM Marks INNER JOIN Student ON Marks.student_id = Student.student_id INNER JOIN Class ON Student.student_id = Class.student_id INNER JOIN MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Class.student_id ORDER BY Hurdle.hurdle_id