0

I have been researching and trying various queries to get this to work. What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will be grouped by student_id. The table with the necessary info is as follows:

assessment_id | student_id | grade | subject_id

What I would like to see after the query is:

student_id | AVG(grade) | rank

I would also like to get the rank of a student using PHP, for example (mysql_query) WHERE student_id = '1001'

The closest I have come to getting this was by using the query below:

SELECT student_id, AVG(grade), (@rn := @rn + 1) AS rank 
FROM grades CROSS JOIN (SELECT @rn := 0) CONST 
GROUP BY student_id ORDER BY AVG(grade) DESC

The problem with the query above is that it ranks the students based on their student_id number.

I have searched for solutions all over but they just seem to not solve what I am after. I would really appreciate your help towards this.

2 Answers 2

1

I can't test this right now, but you probably want something like this:

SELECT student_id, avg, (@rn := @rn + 1) AS rank 
FROM
 (SELECT student_id, AVG(grade) as avg
  FROM grades
  GROUP BY student_id ORDER BY AVG(grade) DESC) agg
CROSS JOIN (SELECT @rn := 0) CONST
ORDER BY avg DESC
3
  • Hope Im not asking too much but is there a way to assign the same rank to averages that matches and then the following student will be ranked at the row number? For example A and B scored 95 so both rank at 4 and then C with a score of 93 will rank at 6.
    – DBeck
    Jul 15, 2016 at 20:48
  • You should probably post it as a new question then. I don't see an easy way of doing that; it might involve calculating the maximum grade of all students in a separate subquery, then computing ranking buckets... Please consider including the table DDL and insert statements with sample data -- this will help those who might try answering.
    – mustaccio
    Jul 15, 2016 at 21:01
  • @mustaccio it can probably be solved with modifying the (@rn := @rn + 1) to use a CASE expression or the IF function (and possibly with the help of an additional variable). Jul 16, 2016 at 11:51
0
SELECT student_id, AVG(grade), RANK() OVER (ORDER BY AVG(grade) DESC) AS rrank
FROM grades
GROUP BY student_id
1
  • Welcome on the DBA SE! Please give also some explanation, how your query works.
    – peterh
    Apr 2, 2020 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.