CODEWITHSUNDEEP
mysql
Mohan
Mohan

Reputation: 420

Doing Calculations in mysql query

I have a user_answer table contains values like this. Basically, i am storing the users response.

userid   category_id    question_id  difficulty_level response    updated_date

1395           1             1              5            0        2014-08-29 00:29:48
1395           1             2              1            1        2014-08-30 11:29:34
1395           1             3              2            0        2014-08-25 08:12:28
1421           1             2              1            1        2014-08-25 06:32:45
1421           1             3              2            1        2014-08-28 01:25:24

response

"0" is wrongly answered

"1" is answered correct

Difficulty Level what we have is 1-5. Each question has different difficulty level(1,2,3,4,5).

I want to calculate the points for each user with category_id. This is method of calculating points(in PHP).

$cur_points = 0;
if(response == 1){
   $cur_points = $cur_points - 1;
}
else if(response == 0){
    if(difficulty_level== 1){
        $cur_points = $cur_points + 5;
    }
    else if(difficulty_level== 2){
        $cur_points = $cur_points + 4;
    }
    else if(difficulty_level== 3){
        $cur_points = $cur_points + 3;
    }
    else if(difficulty_level== 4){
        $cur_points = $cur_points + 2;
    }
   else if(difficulty_level== 5){
        $cur_points = $cur_points + 1;
    }
}

Now this calculation i have to do it MYSQL query itself and result should like this:

 userid   category_id   recently_answered_date           points

 1395           1       2014-08-30 11:29:34                 4
 1421           1       2014-08-28 01:25:24                 3

recently_answered_date will be the recent date which user has answered question in that category.

Is it possible do it? Thanks.

Upvotes: 0

Views: 54

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Use Conditional SUM to add the values you want.

 SELECT user_id, 
        category_id,
        max(updated_date) as recently_answered_date,
        SUM( CASE response
                   WHEN 0 THEN -1
                   ELSE CASE difficulty_level
                             WHEN 1 THEN 5
                             WHEN 2 THEN 4
                             WHEN 3 THEN 3
                             WHEN 4 THEN 2
                             ELSE 1
                        END
             END) as POINTS
   FROM user_answer
   GROUP BY user_id, category_id

NOTE: Still not clear why correct answers '1' remove points on your query, so I guess that was a typo, either on the query or the wrong/correct definition.

Upvotes: 2

Related Questions