# Thread: Group by clause based on condition

1. Registered User
Join Date
Jul 2010
Posts
3

## Unanswered: Group by clause based on condition

tblScore contains score for each problem

id problemID score
------------------------
1 1 10
2 2 30

tblSubmission contains problem submissions for each user

id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1

For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

Now I would like to process the table and want to get the following result :

user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)

Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30

so final score for UserA = 30 + 6 = 36
Similar for UserB.

I know all I am doing is explanations. I have tried a lot with gorup by and other stuffs. May be my lack of knowledge on SQL is the problem.

I would really appreciate if someone help me on this.

Thank You

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Points for correct answers:
Code:
```SELECT tblSubmission.user
, Sum(tblScore.score) As correct_answer_score
FROM   tblSubmission
INNER
JOIN tblScore
ON tblScore.problemID = tblSubmission.problemID
WHERE  tblSubmission.accepted = 1
GROUP
BY tblSubmission.user
;```
Penalty points:
Code:
```SELECT user
, Count(*) As number_of_incorrect_answers
, Count(*) * -2 As penalty_points
FROM   tblSubmission
WHERE  accepted = 0
GROUP
BY user
;```

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Some questions for data and requirements...

(1) Aren't there more than one accepted submissions for a combimation of (user, problemID)?
Are there effective mechanism to prevent such data?

(2) If submissions of a user for a problemID were all failed (and the user gave up to submit anymore for the problemID),
how to score the (user, problemID)?
zero?
-2 * (number of (rejected) submissions)?
For example:
Code:
```id user  problemID accepted
-----------------------------------------------
11 UserC 1         0
12 UserC 1         0
13 UserC 1         0
14 UserC 2         0
15 UserC 2         1
16 UserD 1         0
17 UserD 2         0
18 UserD 2         0
19 UserE 2         0
20 UserE 2         0
21 UserE 2         0```
(3) If (2 * number of rejected submissions) was more than the score of the problemID,
how to score for (user, problemID)?
zero? or minus?
For example:
Code:
```id user  problemID accepted
-----------------------------------------------
22 UserF 1         0
23 UserF 1         0
24 UserF 1         0
25 UserF 1         0
26 UserF 1         0
27 UserF 1         0
28 UserF 1         0
29 UserF 1         1
30 UserF 2         0```
Last edited by tonkuma; 06-17-14 at 13:40. Reason: Add example for (3).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•