Query for total score

Question

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 .... )

score 2 · Accepted Answer · answered Aug 11 '16 at 10:51

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.

RelativePHPNewbie · Answer 2 · 2016-08-11T11:43:48.027

0

Using a CTE

WITH q AS
(
SELECT contestant_id, challenge_id, MAX(score) as score
FROM submissions
GROUP BY contestant_id, challenge_id
)

SELECT contestant_id, SUM(score)
FROM q
GROUP BY contestant_id
HAVING SUM(score) > 0

edited Aug 11 '16 at 11:43

answered Aug 11 '16 at 10:26

RelativePHPNewbie

330
2
11

thanks,but It does not exclude score with 0 totalscore – BlackCat Aug 11 '16 at 10:29
Add `HAVING SUM(score) > 0` at the end. (Or `WHERE score > 0` in the cte.) – jarlh Aug 11 '16 at 10:29
So just add a `HAVING SUM(score) > 0`, see amended answer – RelativePHPNewbie Aug 11 '16 at 10:30
@RelativePHPNewbie , sorry,Its not giving desired output. For contestant_id 1 it should give total score as 170 . – BlackCat Aug 11 '16 at 10:47
This is because the CTE mistakenly groups by submission_id. Remove submission_id from the CTE and the query works fine. – Thorsten Kettner Aug 11 '16 at 11:33
@RelativePHPNewbie: Such errors are easier to spot when you explain what your query is doing (such as Gordon does in his answer). You would have probably spotted it yourself when writing the explanation. It also makes it easier for the OP to understand what the query does. – Thorsten Kettner Aug 11 '16 at 12:19
@Zahid: Complaining that the results contain rows with a score of zero and not knowing that this asks for a having clause shows that you are either an absolute beginner (then you shouldn't ask such questions here, but learn the SQL basics first) or just too lazy to think about the query given (which is even worse). – Thorsten Kettner Aug 11 '16 at 12:19

Query for total score

2 Answers2

Linked