Graph showing comparative analysis of students class performance.

[Olumide]

I want to generate a bar chart that will show the score of students in each subject and the mean. I am a bit confused in the mean aspect as what I am getting is far below my expected result. I would appreciate if more light can be shed on how to go about this getting the mean. I have the php code below and the output of the graph.

 

$res = $pdo->prepare("SELECT s.subjectname,
                            SUM(r.score) AS student_score,
                            SUM(r.score) / COUNT(r.score) AS mean
                     FROM result r
                          JOIN student_class sc ON r.studentclassid = sc.id
                          JOIN course c ON r.courseid = c.id
                          JOIN subject s ON c.subjectid = s.id
                          JOIN semester sm ON sc.semesterid = sm.id
                     WHERE sm.id = ?
                       AND sc.classid = ?
                       AND sc.studentid = ?
                       AND sm.semestername+0 <= 3
                     GROUP BY s.subjectname
                     ORDER BY s.subjectname");
$res->execute([$semester, $clid, $student]);
$data = $res->fetchAll(PDO::FETCH_ASSOC);

// Prepare data for JSON response
$response = [
    'labels' => [],
    'scores' => [],
    'classAvg' => []
];

foreach ($data as $row) {
    $response['labels'][] = $row['subjectname'];
    $response['scores'][] = (int) $row['student_score'];
    $response['classAvg'][] = round((float) $row['mean'], 2);
}

// Return data in JSON format
header('Content-Type: application/json');
echo json_encode($response);
exit;

 

[Barand]

Let's look at the mathematics scores in your example images above.

SUM(score) for the term 68 (ie 5+8+10+45) and those give a mean value of (5+8+10+45)/4 = 17.

Those correctly reflect the values shown by your chart.

So the questions is "what average are you wanting to show if that is wrong?"

[Olumide]

Thank you @Barand for the clarification. But now, I changed to Class Average, the Class Average I have in the Chart is different from the Class Average in the result table as shown in the attached images, and I used the same query used for the result class average for the chart too.

 

SELECT s.subjectname,
                            SUM(r.score) AS student_score,
                            round(AVG(CASE r.exam
                                           WHEN 'Exam' THEN r.score * 100 / 70
                                           ELSE r.score * 10
                                         END), 2) AS mean
                     FROM result r

 

 

[Barand]

8 hours ago, Olumide said:

round(AVG(CASE r.exam
                                           WHEN 'Exam' THEN r.score * 100 / 70
                                           ELSE r.score * 10
                                         END), 2) AS mean

That is not the class average, it's the average score by the student in each of the four term tests

The class average would be the total scores attained by all students in the class for the subject divided by the number of students.

[Olumide]

4 minutes ago, Barand said:

That is not the class average, it's the average score by the student in each of the four term tests

The class average would be the total scores attained by all students in the class for the subject divided by the number of students.

Thanks for the clarification, but if I may ask, why is the graph not capturing the same class average in the result table and for the class average as you stated above, consider a situation whereby there are 20 students in a class and 16 out of the 20 students took Biology, will the class average still be total scores attained by all students divided by the number of students or divided by the number of students who took the subject?

Thanks

[Barand]

Divide total by the number of scores for the subject.

14 minutes ago, Olumide said:

but if I may ask, why is the graph not capturing the same class average in the result table and for the class average as you stated above

Your query is calculating average for each student's scores, not for the class

[Barand]

This query uses WINDOW function to get the class averages for each subject. (I am using MariaDB 11.1 but MySql 8 also has them). If you don't have then then us a subquery to get the class averages and join to that.

SELECT studentid
     , subjectname
     , student_score
     , ROUND(AVG(student_score) OVER (PARTITION BY subjectname)) as mean
FROM (
		SELECT 
			   sc.semesterid, 
			   sc.classid,
			   sc.studentid,
			   s.subjectname,
			   SUM(r.score) AS student_score
		 FROM result r
			  JOIN student_class sc ON r.studentclassid = sc.id
			  JOIN course c ON r.courseid = c.id
			  JOIN subject s ON c.subjectid = s.id
			  JOIN semester sm ON sc.semesterid = sm.id
		WHERE sm.id = 10
			  AND sc.classid = 1
		 GROUP BY subjectname, studentid
) totals
ORDER BY studentid, subjectname;

 

[Olumide]

Thank you @Barand I will run the query when I am on PC, currently on phone.

[Olumide]

On 1/11/2024 at 4:42 PM, Barand said:

This query uses WINDOW function to get the class averages for each subject. (I am using MariaDB 11.1 but MySql 8 also has them). If you don't have then then us a subquery to get the class averages and join to that.

SELECT studentid
     , subjectname
     , student_score
     , ROUND(AVG(student_score) OVER (PARTITION BY subjectname)) as mean
FROM (
		SELECT 
			   sc.semesterid, 
			   sc.classid,
			   sc.studentid,
			   s.subjectname,
			   SUM(r.score) AS student_score
		 FROM result r
			  JOIN student_class sc ON r.studentclassid = sc.id
			  JOIN course c ON r.courseid = c.id
			  JOIN subject s ON c.subjectid = s.id
			  JOIN semester sm ON sc.semesterid = sm.id
		WHERE sm.id = 10
			  AND sc.classid = 1
		 GROUP BY subjectname, studentid
) totals
ORDER BY studentid, subjectname;

 

Thank you @Barand for your brilliant contribution and experienced contribution.