title: Creating Medalist MySQL view from a table
body: <p>I need a help to create a view in MySQL.</p>
  <p>I have a table in the name of competitions like below:</p>
  <pre><code>+---------+-----+-----+-----+
  |id| name |rank1|rank2|rank3| 
  +--+------+-----+-----+-----+
  | 1| cmpt1| 4   | 3   | 9   |
  | 2| cmpt2| 3   | 7   | 8   |
  | 3| cmpt3| 4   | 1   | 2   |
  | 4| cmpt4| 5   | 8   | 4   |
  | 5| cmpt5| 9   | 3   | 2   |
  | 6| cmpt6| 1   | 8   | 2   |
  +--+------+-----+-----+-----+
  </code></pre>
  <p>the rank1,2,3 values refer to the player id who has such rank at the end of that competition.</p>
  <p>Now I want to create a MySQL view to show each player's total medals. Rank 1, 2, and 3 received gold, silver, and bronze medal respectively.</p>
  <p>The output of the view will be like following table:</p>
  <pre><code>+------+------------+-------------+-------------+
  |player| gold_medals|silver_medals|bronze_medals| 
  +------+------------+-------------+-------------+
  | 1    |     4      |     7       |     1       |
  | 2    |     7      |     0       |     9       |
  | 3    |     1      |     4       |     6       |
  | 4    |     0      |     2       |     8       |
  | 5    |     2      |     8       |     0       |
  | 6    |     3      |     1       |     1       |
  +------+------------+-------------+-------------+
  </code></pre>
  <p>Thanks in advance</p>