mysql在考试中获得学生排名

I have the following query.

SELECT 
  SUM(
    marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
  ),
  FIND_IN_SET(
    SUM(
      marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
    ),
    (SELECT 
      GROUP_CONCAT(summarks 
        ORDER BY summarks DESC) 
    FROM
      (SELECT 
        SUM(
          marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
        ) AS summarks 
      FROM
        results 
      GROUP BY student_id) sm)
  ) AS rank 
FROM
  results 
WHERE student_id = ".$student_id." 
  AND exam_year = ".$year." 
  AND exam_id = ".$exam." 
  AND results.class_id = ".$class_id." 
  AND section_id = ".$section_id." 

This query works fine for me only when there in one exam in my result table. When there are more exams,this query calculates marks for all exams and return rank to me. But In case I need student rank for only one exam, I need to put exam_id in the query and when I do that, the query returns 0 as student rank.

In above picture, I got 0th position which is invalid.But if i remove the exam_id condition,then I get the correct rank,but the problem is it sums marks for all entered exams which i dont want as I need rank for a specific exam.

So please anyone tell me where should I put the exam_id condition in my query.

Try to put the exam_id in your sub query too.

(SELECT 
      GROUP_CONCAT(summarks 
        ORDER BY summarks DESC) 
    FROM
      (SELECT 
        SUM(
          marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
        ) AS summarks 
      FROM
        results WHERE exam_id = ?
      GROUP BY student_id