Zahid Zahid - 1 year ago 67
SQL Question

Query for total score

Input Table: Submissions

Submission contains four columns , Contestant_id indicates id of different contestants who submit against different problems.One contestant can submit against a problem more than one time.So,challenge_id may appear more than once against a Contestant_id.

submission_id Contestant_id challenge_id score
11 1 333 90
22 2 333 60
33 3 333 80
44 4 333 0
112 1 333 45
113 1 444 80
114 2 444 70


Output Table: Total Score

Contestant_id score
1 170
2 130
3 80


Here, we take total score as-

for contestant_id 1 : total score = max(90,45)+ 80 = 170
for contestant_id 2 : total score = 60 + 70 = 130
for contestant_id 3 : total score = 80
for contestant_id 4 : total score = 0 ;so we exclude it


for taking total score, I have to take maximum of a single contestant_id,if there is more than one same challenge_id,than take a sum.But,I am stuck at it,how to do.

Select Contestant_id,Score as
(Select sum (max(Select .... )

Answer Source

You seem to need two levels of aggregation. You need the maximum score for each contestant and challenge. Then you want to add these up.

Here is one method:

select Contestant_id, sum(max_score)
from (select Contestant_id, challenge_id, max(score) as max_score
      from Submissions
      group by Contestant_id, challenge_id,
     ) t
group by Contestant_id;

If you want to create an output table, you can add into total_scores after the select.