CODEWITHSUNDEEP
mysql
Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4038

MySQL calculating success rate based on column values

I have the table

t1

this tells whether the jobs are done by the user

finished -> user successfully finished the job

given -> employer assigned the job to the user but the work is not yet done

estopped -> there is some problem, so the employer canceled the contract

id userid jobid  status
1    4      6    finished
2    4      7    given
3    4      8    estopped
4    4      9    finished
2    4      10    given
3    4      11    finished
4    4      12    finished

Now I want to calculate the success rate,

((total number of finished by user 4)/ ((total number of finished by user 4)+(total number of estopped by user 4))) *100

I don't how to implement this in MySQL ...

Upvotes: 1

Views: 719

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

You can use the following solution:

SELECT ((c_finished / (c_finished + c_estopped)) * 100) AS success_rate
FROM (
    SELECT 
        userid,
        SUM(CASE WHEN status = 'finished' THEN 1 ELSE 0 END) AS c_finished,
        SUM(CASE WHEN status = 'given' THEN 1 ELSE 0 END) AS c_given,
        SUM(CASE WHEN status = 'estopped' THEN 1 ELSE 0 END) AS c_estopped
    FROM t1
    GROUP BY userid
)x WHERE x.userid = 4

Upvotes: 1

Keyur Panchal
Keyur Panchal

Reputation: 1402

Try below query: 

select userid,
( ( sum(case when status='finished' then 1 else 0 end) * 100 ) /
  ( sum(case when status='finished' then 1 else 0 end) + sum(case when status='estopped' then 1 else 0 end) )
) success_rate
from t1
where userid = 4
group by userid;

If you want records for all the users, simply remove condition- where userid = 4 from query.

Upvotes: 3

Related Questions