Nyein Chan Nyein Chan - 12 days ago 5
MySQL Question

How to get one column value depend on another

I have data in my table like below. (There are many other columns in my table)

project_id | done_ratio
1510 ------------ 100
1510 ------------ 100
1510 ------------ 100
1511 ------------ 80
1511 ------------ 90
1511 ------------ 0
1512 ------------ 0
1512 ------------ 0


The query I want to write is get status depend on the done_ratio like following.

status for project_id 1510 is done because all done ratio is 100.

status for project_id 1511 is working because all done ratio is not 100 and 0.

status for project_id 1512 is not_start because all done ratio is 0.

Can anyone explain me?

Answer
select project_id,
       case when avg(done_ratio) = 100 then 'done'
            when avg(done_ratio) > 0 then 'working'
            else 'not_start'
       end as status
from your_table
group by project_id
Comments