I want to change this SQL query to output CA1, CA2, Total of the two CA and populate the table accordingly

[Olumide]

if ($clid <= 0) {
        $res = $pdo->prepare("SELECT classid
                             FROM student_class
                             WHERE semesterid = ?
                                   AND studentid = ?
                            ");
        $res->execute( [ $semester, $student ]);
        $clid = $res->fetchColumn();
    }

################################################################################
#  Get scores and put in array with required output structure                  #
################################################################################

        $studentname = '';
        $studentlevel = '';
        $studentsession = '';
        $studentsemester = '';
        $studentterm = '';
        $passport = '';
        $level = '4';
        $pupil_count = 0;
        $grand_total = 0;
        $subject_count = 0;

    $res = $pdo->prepare("SELECT st.id as stid
                                 , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname
                                 , st.image
                                 , cl.classname
                                 , sc.classid
                                 , l.id as level
                                 , c.subjectid
                                 , s.subjectname
                                 , sn.sessionname
                                 , 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
                                 
                            WHERE sn.id = ?
                              AND studentid = ?
                              AND sm.semestername+0 = ?
                              
                            ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam
                            ");
    $res->execute( [ $session, $student, $termno ] );
    $data = [];
    $subject_count = 0;
    // get data common to all rows from first row
    $r = $res->fetch();
    if ($r) {
        $studentname = $r['stname'];
        $studentlevel = $r['classname'];
        $studentsession = $r['sessionname'];
        $studentterm = "- Term $termno";
        $level = $r['level'];
        $passport = "images/" . $r['image'];
        // then process the rest of the row data in the first and remaining rows
        do {
            if (!isset($data[ $r['subjectid'] ])) {
                $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'],
                                             #'exams' => ['CA1'=>'', 'CA2'=>''],
                                             #'ca'  => [ 1=>0, 0, 0 ],
                                             'ca' => 0,
                                             'last'  => 0,
                                             'avg' => 0, 
                                             'terms' => 0
                                           ];
            }   
            $data[ $r['subjectid'] ]['ca'] = $r['ca'];
            $subject_count += ($r['ca'] > 0);
        } while ($r = $res->fetch());
//        $subject_count = count($data);
//        if ($subject_count == 0) $subject_count = 1;
        
################################################################################
#  get prev terms' totals
################################################################################ 
        $res = $pdo->prepare("SELECT c.subjectid
                                     , round(sum(score) ) as lastterm
                                     , count(distinct sm.id) as terms
                                FROM result r 
                                     JOIN course c ON r.courseid = c.id
                                     JOIN student_class stc ON r.studentclassid = stc.id
                                     JOIN semester sm ON stc.semesterid = sm.id
                                WHERE sm.sessionid = ?
                                      AND stc.studentid = ?
                                      AND sm.semestername+0 <= ?
                                GROUP BY c.subjectid
                                ");
        $t1 = $termno - 1;
        $res->execute([ $session, $student, $t1 ]);
        foreach ($res as $r) {
            
            if (isset($data[$r['subjectid']])) {
                $data[$r['subjectid']]['last'] = $r['lastterm'];
                $data[$r['subjectid']]['terms'] = $r['terms'];
            }
            
        }
################################################################################
#  get the avg scores for the class                                            #
################################################################################
        $avgs = classAverageScores($pdo, $clid, $session, $termno);
        foreach ($avgs as $s => $av) {
            if (isset($data[$s]))
                $data[$s]['avg'] = $av;
        }   
################################################################################
#  Get pupil count                                                             #
################################################################################
        $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils
                                FROM student_class stc 
                                     JOIN semester sm ON sm.id = stc.semesterid
                                     JOIN result r ON stc.id = r.studentclassid
                                WHERE sm.id = ?
                                  AND stc.classid = ?
                                ");
        $res->execute([ $semester, $clid ]);
        $pupil_count = $res->fetchColumn();    
    }
    else {
//        $studentname = '';
//        $studentlevel = '';
//        $studentsession = '';
//        $studentsemester = '';
//        $studentterm = '';
//        $passport = '';
//        $level = '4';
//        $pupil_count = 0;
//        $grand_total = 0;
//        $subject_count = 1;
//        $clid = 0;
    }
        
################################################################################
#  Loop through the data array to construct the output table rows              #
################################################################################
        $tdata = '';
        $n = 1;
        $grand_total = 0;
//        $subject_count = count($data);
//        if ($subject_count==0) $subject_count = 1;
        foreach ($data as $subid => $subdata) {
            $tdata .= "<tr><td>$n</td>
                           <td>{$subdata['name']}</td>";
            $tdata .= "<td>{$subdata['ca']}</td>";
            //$total = round(($subdata['last'] + $subdata['ca'])/($subdata['terms']+1));
            $total = $subdata['ca'];
            $grand_total += $total;
            if ($total > 0) {
                list($grade, $comment) = getGradeComment($pdo, $total, $level);
            }
            else {
                $grade = '-';
                $comment = '-';
            }
            $clr = GRADE_COLOUR[$grade] ?? '#000';
            $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n";
            ++$n;
        }
################################################################################
#  Get list of gradings                                                        #
################################################################################

$grade_list = getGradeList($pdo, $level);

################################################################################
#  Get end of term comments                                                    #
################################################################################

$comments = getEOTComments($pdo, $student, $semester);

?>

Please, I am still learning, this was designed by a Boss and my Mentor, he was so good to me but something transpired that made him to forsake me and here am I now stalked. I was instructed to modify this to suit our school mid term report. We do generate Mid Term Report for our student, which is usually done using the CA1, but now we need to add CA2. Each CA have a maximum score entry in the database which is maximum of 10, but while outputting it, the score will be multiply by 5 and CA1 + CA2 will be the total because the total will decide the grades and the total highest mark is 100 also. I have tried series of methods to add  CA2 from the SQL query but not working for me

From the picture attached (res.png), CA1 and CA2 values are different, and the value show here is only for CA1 while it is not outputting values for CA2. Please help me on how to fix this.