CODEWITHSUNDEEP
sqlsql-serverdatabase
BlackCat
BlackCat

Reputation: 2044

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

Upvotes: 3

Views: 1550

Answers (2)

Chris
Chris

Reputation: 340

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

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.

Upvotes: 2

Related Questions