Sum for distinguished values ​​in MySQL

Question

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

This table (called contest_submissions) stores the relationship of applications and tenders.

ContestID | SubmissionID

1           1000
1           1001
1           1002
1           1003

The second table (called submissions) stores detailed information about the view:

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) consists 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 the 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 the following result:

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

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

What should I do?

By the way, I'm using MySQL.

Answer 1

- :

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

. , 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;

, submissions(user, ProblemId, score, submissionId) , .

Answer 3

You can use the socket request - internal to get the best user response for each problem, and external - to summarize them:

SELECT   user, SUM(score) AS total_score
FROM     (SELECT   user, problemid, MAX(score) AS score
          FROM     submission
          GROUP BY user, problemid) t
GROUP BY user