Calculate the Ranks of Candidates based on Votes and Total Candidates

Question

What is the formula to calculate the rank of each candidate when I have the total candidates and votes secured by each?

I've managed the percentage part, but calculating the rank has me stuck.

Id be glad if you could help with just the formula. Just like the formula for interest is PTR/100, what would be the formula to calculate this?

Total Candidates

5

Total Votes

75

Votes

Name    Votes   Percentage Rank(What I'm trying to calculate)
A       25      33.34       1/5 ->Rank 1/5 has the most votes
B       20      26.67       2/5 ->And so on. Never mind the /5 part
C       10      13.34       4/5
D        5      6.67        5/5
E       15      20.00       3/5

Answer 1

SELECT A.*, count(B.*) as Rank FROM mytable as A, mytable as B WHERE A.Votes <= B.Votes

Answer 2

We will use the indicator function $\mathbf{1}\{ x\}$, which is 1 if condition $x$ is true, and 0 if it is not.

Let $v_i$ be the number of votes for candidate $i$. Then rank $r_i$ of candidate $i$ is $$r_i= N-\sum_{j\neq i} \mathbf{1}\{ v_i\ge v_j\}.$$ In your example, $N=5$ is the number of candidates, and to compute the rank of $i$, you subtract 1 every time there is a candidate among all $j\neq i$ who has fewer votes. The identical ranking is achieved if you just count the number of of candidates with strictly more votes (+1): $$r_i=1+\sum_{j\neq i}\mathbf{1}\{ v_i< v_j\}.$$

According to the formula, if there are two candidates who have the same number of votes, then they have the same rank. Suppose C and E both have $v=10$ in your example. Then they would both get rank $r=4$, and the next best rank would be rank 2 for B (nobody has rank 3).