title: MySQL query to count and score multiple columns
body: <p>I have a MySQL table that looks like the following:</p>
  
  <pre><code>player_rankings | CREATE TABLE `player_rankings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `award_id` int(11) NOT NULL,
    `rank_1` int(11) DEFAULT NULL,
    `rank_2` int(11) DEFAULT NULL,
    `rank_3` int(11) DEFAULT NULL,
    `rank_4` int(11) DEFAULT NULL,
    `rank_5` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_id` (`user_id`,`award_id`)
  ) 
  </code></pre>
  
  <p><code>rank_1</code> through <code>rank_5</code> contain ids of players that I have in a different table, but for the purpose of this query, it isn't relevant. What I am trying to do is count how many times a value shows up in each <code>rank_*</code> column and then give it the following amount of points:</p>
  
  <ul>
  <li><code>rank_1</code> - 5 points</li>
  <li><code>rank_2</code> - 4 points</li>
  <li><code>rank_3</code> - 3 points</li>
  <li><code>rank_4</code> - 2 points</li>
  <li><code>rank_1</code> - 1 point</li>
  </ul>
  
  <p>The end result would sort from highest to lowest (descending) the player ids with their total point count. </p>