How do I rank the total score in order of positions

Question

How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position

CREATE TABLE `scores` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` int(11) default NULL,
  `sessions_id` varchar(11) default NULL,
  `term_id` int(11) default NULL,
  `ass1` int(11) default NULL,
  `test1` int(11) default NULL,
  `proj1` int(11) default NULL,
  `ass2` int(11) default NULL,
  `test2` int(11) default NULL,
  `proj2` int(11) default NULL,
  `exam` int(11) default NULL,
  `class_id` int(11) default NULL,
  `subject_id` int(11) default NULL,
  `total_score` int(11) default NULL,
  `class_average` decimal(6,2) default NULL,
  `highest_score` int(11) default NULL,
  `lowest_score` int(11) default NULL,
  `position_in_subject` int(11) default NULL,
  `grade` varchar(2) default NULL,
  `teacher_remark` varchar(50) default NULL,
  `date_created` varchar(20) default NULL,
  `employee_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

and here is my query

INSERT INTO `scores` VALUES ('1', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('2', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('3', '1', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('8', '5', '2014/2015', '2', '5', '6', '6', '6', '6', '6', '6', '1', '7', '41', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('9', '5', '2014/2015', '2', '5', '4', '5', '4', '5', '4', '34', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('10', '5', '2014/2015', '2', '5', '4', '4', '5', '5', '4', '5', '1', '9', '32', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('15', '8', '2014/2015', '2', '6', '6', '6', '6', '7', '7', '7', '1', '7', '45', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('16', '8', '2014/2015', '2', '4', '3', '4', '3', '4', '3', '40', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('17', '8', '2014/2015', '2', '4', '5', '4', '4', '5', '4', '23', '1', '9', '49', '35.00', '63', '22', null, '', null, '2015-04-05', '1');

-- suggestion include a student with the same total so that calculating the rank can be QA in a proper way

INSERT INTO `scores` VALUES ('4', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('5', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('6', '2', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');

Answer 1

I hate giving complete solutions but here you go:

START TRANSACTION;
create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc
FROM
    scores
GROUP BY student_id);
select student_id,  tot_sc,
FIND_IN_SET(
tot_sc
, (SELECT 
    GROUP_CONCAT(
    distinct tot_sc
        ORDER BY tot_sc DESC) AS score
FROM
    total_sc_tmp)) as rank from total_sc_tmp;
  COMMIT;

reorder, use @row and avoid the tmp table if you want. Happy Easter to all of you with constructive comments

Answer 2

SELECT  id,
        @seq := @seq + 1 AS seq,
        @rank := if(total_score = @sc, @rank, @seq) AS Rank,
        @sc := total_score AS Score
    FROM   ( SELECT  @sc := -1, @seq := 0, @rank := 0 ) init
    JOIN  scores
    ORDER BY  total_score DESC, id; 

To get rid of id and seq:

SELECT Rank, Score FROM ( <the SELECT above> ) ORDER BY Rank, id;

I'm unclear on whether total_score it the score for ranking or the SUM over student_id or the MAX for each "student". Depending on the intent, you may need to replace JOIN scores with JOIN ( <some subquery to get SUM or MAX> )