how to generate sql query for the summation of specific column and then rank it

Question

i have a table mark_summery, Here is my table structure

now i just want to sum the "obt_marks" and "total_marks" of specific student with multiple exams (maximum four). "multiple exams mean summation of four exams " how can i sql it. any idea.?

Answer 1

Try using this code

select s.StudentID, s.name, sum(marks)
from tbl_marks m inner join tbl_student s
on m.studentid=s.studentid
group by s.studentid
having sum(marks)=
(
select max(tbl.totalmarks) from 
(select studentid, sum(marks) as totalmarks from tbl_marks group by studentid) tbl
)

Try using this code

Answer 2

You could use sum group by on a subqyery for the number of exam for each student

select student_id, sum(obt_marks), sum(total_marks)
from my_table 
inner join (
    select student_id, count(exam_id) num_exam
    from my_table 
    group by student_id
 ) t on t.student = my_table.student_id and t.num_exam <= 4 
 group by student_id

Answer 3

You could create a metod which fetches the data and into you're controller you could sum it with a foreach

    $user = $this->User_model->total_activity($id);
    $sum = 0;

    foreach ($user as $key) {
         if ($key['db_field'] !== null) {
            $sum += $key['db_field']);
         }
    }
    return $sum;