CODEWITHSUNDEEP
mysqldatabase
samayo
samayo

Reputation: 16495

Mysql, ranking users by percentage

I have a table like this that keeps a total score of a history exam for students. 

+----+---------------+-------+---------+-
| id | name          | history |  rank  | 
+----+---------------+-------+---------+-
|  1 | yngiid        |    97   |        | 
|  2 | viyrp         |   217   |        | 
|  3 | pae           |   599   |        | 
|  4 | spohl         |   284   |        | 
|  5 | shl           |   295   |        | 
|  6 | okeer         |    73   |        | 
|  7 | jmaany        |   657   |        | 
|  8 | hxt           |    80   |        | 
|  9 | yanier        |   599   |        | 
+----+---------------+-------+----------+-

The history field as you can see contains the total score the student . Now, I would like to rank the students by their scores as in, the higher the points the lower the rank, but only using percentage. Which means, user can be ranked from 1 - 100 only, basically similar to what stackoverflow has on the profile page, where it says .. top 1%

I tried something like this. 

SELECT NAME, HISTORY, ROUND(history / 100) AS percentage FROM test ORDER BY percentage DESC

For which I was only able to get this. 

name    HISTORY  percentage  
------  -------  ------------
jmaany      657             7
yanier      599             6
pae         599             6
shl         295             3
spohl       284             3
viyrp       217             2
yngiid       97             1
yngiid       97             1
okeer        73             1
yngiid       97             1
hxt          80             1
yngiid       97             1

The above is wrong, because (1) percentage should be from 1 - 100% and user with the highest HISTORY points should get the lowest percentage, so as to say TOP 1% or something similar.

Here is a sample table dump if it helps. 

create table `test` (
    `id` int (11),
    `name` varchar (765),
    `history` int (11),
    `rank` int (11)
); 

insert into `test` (`id`, `name`, `history`, `rank`) values
('1','yngiid','97',NULL),
('2','viyrp','217',NULL),
('3','pae','599',NULL),
('4','spohl','284',NULL),
('5','shl','295',NULL),
('6','okeer','73',NULL),
('7','jmaany','657',NULL),
('8','hxt','80',NULL),
('9','yanier','599',NULL);

Upvotes: 1

Views: 693

Answers (1)

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4226

basically similar to what stackoverflow has on the profile page, where it says .. top 1%

The logic stackoverflow displays its top 1%, etc. rank is quit easy to write. It means actually that the user is among top 1% of all users. First for every user we have to determine the number of users that have rating (history) greater or equal. Then apply simple formula numberOfUsersHavingGreaterOrEqualRating / numberofTotalUsers * 100.

SET @total= (SELECT COUNT(*) FROM stud);
select s1.*, concat('TOP ', floor(nge / @total * 100), '%') as percent 
from stud s1
join (select s1.id, count(s2.id) as nge
    from stud s1
    join stud s2
    on s1.history <= s2.history
    group by s1.id) as s2
on s1.id = s2.id
order by history desc

Play with it

Upvotes: 1

Related Questions