Haozhe Xie Haozhe Xie - 6 months ago 11
SQL Question

Sum for Distinct values in MySQL

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

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