Hamzat Luqman Hamzat Luqman - 1 month ago 18
MySQL Question

Php and Mysql to rank student based on their total scores and give . Also not rank consecutively if their is ties

I have a very simple MySQL table where I save subjects with Exam and CA Scores for each students with their admin_no. It looks like that:

admin_no subject ca exam year class_s
10/00182 IRS 39 56 2014/2015 Grade 2
10/00177 English 39 59 2014/2015 Grade 2
10/00177 Mathematics 34 59 2014/2015 Grade 2
10/00177 Basic 37 59 2014/2015 Grade 2
10/00177 Social 39 60 2014/2015 Grade 2
10/00177 Yoruba 33 59 2014/2015 Grade 2
09/00159 English 37 59 2014/2015 Grade 2
09/00159 Mathematics 35 60 2014/2015 Grade 2
09/00159 Basic 39 59 2014/2015 Grade 2
.......................................................


I used the mysql query bellow to sum but exam scores and CA of all the subjects and then total all the scores of each students as total_scores and then rank the total scores:

SELECT admin_no,rank,total_score
FROM (SELECT *, IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank
FROM (SELECT * FROM
(SELECT admin_no, SUM(exam)+SUM(ca) AS total_score,year,class_s
FROM subjects_1 ,
(SELECT @auto:=0, @marks:=0) as init WHERE `class_s`='Grade 2' and `year`='2014/2015'
GROUP BY admin_no ) sub ORDER BY total_score DESC)t) as result


The Output of the query:

admin_no rank total_score
08/00076 1 1615
10/00170 2 1613
12/00300 3 1609
09/00091 4 1604
10/00182 5 1600
09/00159 6 1583
10/00177 7 1574
09/00152 8 1561
09/00165 9 1540
10/00176 10 1516
13/00354 11 1497
10/00178 12 1470
14/00348 13 1409
**14/00346 14 12
15/00371 14 12
09/00156 15 7**


Problems:The out put is good but having problem with tallies in the last three ranks .i.e the total scores 12 appear twice for 14/00346 and 15/00371 and they were given the same rank which is good but next to 12 is 7 and it is ranked 15 instead of 17. Pls help me I dont want the Rank to be consecutive if their is ties in the total_scores .

Bellow is a copy of my mysql data
http://youth-arena.com/portal/sql.sql

Here are the php query codes

http://youth-arena.com/portal/query.txt

Answer

Try this.

I've not included year and class_s in there as you may not get a reliable rowset (as you're GROUPing by admin_no).

I've included both ordinal and competition ranks - pick whichever one you need.

More info here: https://en.wikipedia.org/wiki/Ranking

I don't know the guidelines of who gets the higher rank when tied, but as a suggestion - you could do the number of subject a student studies to determine this (included in SQL for example purposes).

SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
SELECT @rank_increasing := @rank_increasing + 1 AS ordinal_rank
     , CASE
       WHEN @prev_value = a.total_score
          THEN @rank_count
       WHEN @prev_value := a.total_score
          THEN @rank_count := @rank_increasing 
        END AS competition_rank
     , a.*
  FROM ( SELECT admin_no
              , SUM(exam) + SUM(ca) AS total_score
              , SUM(exam) AS sum_exam
              , SUM(ca) AS sum_ca
              , COUNT(DISTINCT subject) AS subject_count
           FROM subjects_1
          GROUP BY admin_no
          ORDER BY total_score DESC
       ) a

Screenshot snippet

enter image description here

Comments