title: Sum fields created with case when statement
body: <p>I have a table with game scores; one row for each mission played. To get the best scores for each user, I created a a query that will give me the maximum score for each mission per user with one row per user. These scores are added as additional columns (mission_1 and mission_2). So far so good.</p>
  
  <p>But now I want to sum the these two fields to get the total max score for each user. However, MySQL won't let me and says Unknown column 'mission_1' in 'field list'.</p>
  
  <p>Is it possible to get these fields summed somehow?</p>
  
  <p>I'm also wondering if it would be possible to get the corresponding row_ids (gsid in my table) for the mission with the maximum score in separate columns (mission_1_id and mission_2_id). But I have a feeling that won't work because I'm using MAX().</p>
  
  <pre><code>SELECT m . * , max( m.id ) as max_id, max( submitted_on ) AS max_submitted_on, 
  ifnull( max(CASE WHEN mission =1 THEN score END ) , 0 ) AS mission_1_score, 
  ifnull( max(CASE WHEN mission =2 THEN score END ) , 0 ) AS mission_2_score,
  mission_1_score + mission_2_score AS total_score
  FROM game_sessions m
  GROUP BY username
  </code></pre>