Sum for Distinct values in MySQL

Question

I have three tables, the structure is listed as following.

This table (called contest_submissions) stores the relationship of submissions and contests.

ContestID | SubmissionID

1           1000
1           1001
1           1002
1           1003

The second table (called submissions) stores the detail information of a submission:

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

Another table (called contest_contestants) is consisted of:

ContestID | User

1           A
1           B

I wrote the following SQL:

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

I got following result (Suppose ContestID = 1):

contestID | User | Total Score | Total Time
1           A      190           3750
1           B      20            1750

where 190 = 100 + 40 + 50.

However, I want to get following result:

contestID | User | Total Score | Total Time
1           A      150           2500
1           B      20            1750

where 150 = MAX(100, 40) + 50, because 100 and 40 come from the same problem (with the same ProblemID).

What should I do?

BTW, I'm using MySQL.

Answer 1

you can try something like that:

select User, sum(MaxScore)
from
(
select User, ProblemID, max(Score) as MaxScore
from submissions
group by User, ProblemId
) as t
group by User

Answer 2

Hmmm. I think there is a way to do this with only one group by:

select s.user, sum(s.score)
from submissions s
where s.submissionId = (select s2.submissionId
                        from submissions s2
                        where s2.user = s.user and s2.ProblemId = s.ProblemId
                        order by s2.score desc
                        limit 1
                       )
group by s.user;

I offer this as a solution because with an index on submissions(user, ProblemId, score, submissionId) it should have somewhat better performance than the solutions with two aggregations.