title: Sum for Distinct values in MySQL
body: <p>I have three tables, the structure is listed as following. </p>
  
  <p>This table (called <code>contest_submissions</code>) stores the relationship of submissions and contests.</p>
  
  <pre><code>ContestID | SubmissionID
  
  1           1000
  1           1001
  1           1002
  1           1003
  </code></pre>
  
  <p>The second table (called <code>submissions</code>) stores the detail information of a submission:</p>
  
  <pre><code>SubmissionID | ProblemID | User | Score | Time
  
  1000           1000        A      100     1000
  1001           1000        A      40      1250
  1002           1001        A      50      1500
  1003           1001        B      20      1750
  </code></pre>
  
  <p>Another table (called <code>contest_contestants</code>) is consisted of:</p>
  
  <pre><code>ContestID | User
  
  1           A
  1           B
  </code></pre>
  
  <p>I wrote the following SQL:</p>
  
  <pre><code>SELECT *, (
      SELECT SUM(score)
      FROM  contest_submissions cs
      NATURAL JOIN submissions
      WHERE user = cc.user
      AND SubmissionID = cs.SubmissionID
  ) AS TotalScore, (
      SELECT SUM(Time)
      FROM contest_submissions cs
      NATURAL JOIN submissions
      WHERE user = cc.user
      AND SubmissionID = cs.SubmissionID
  ) AS TotalTime
  FROM contest_contestants cc
  WHERE contestID = 1
  </code></pre>
  
  <p>I got following result (Suppose <code>ContestID = 1</code>):</p>
  
  <pre><code>contestID | User | Total Score | Total Time
  1           A      190           3750
  1           B      20            1750
  </code></pre>
  
  <p>where <code>190 = 100 + 40 + 50</code>.</p>
  
  <p>However, I want to get following result:</p>
  
  <pre><code>contestID | User | Total Score | Total Time
  1           A      150           2500
  1           B      20            1750
  </code></pre>
  
  <p>where <code>150 = MAX(100, 40) + 50</code>, because <code>100</code> and <code>40</code> come from the same problem (with the same <code>ProblemID</code>).</p>
  
  <p>What should I do? </p>
  
  <p>BTW, I'm using MySQL.</p>