What is the best way to get a derived status column based on existing result

Question

I have a table :

------------------------
testId |    runId   |result 
------------------------
**1    | 11         |0**
**1    | 12         |1**
**1    | 13         |1**
**1    | 14         |1**
**2    | 21         |0**
**3    | 31         |1**
**4    | 41         |1**
**4    | 42         |1**
**4    | 43         |1**
**5    | 51         |0**
**5    | 52         |0**
**5    | 53         |0**
**6    | 61         |1**
**6    | 62         |0**
**6    | 63         |1**

---

For a test there can be multiple run/execution. Each run have a result. here for result column, 0 is fail and 1 is pass. I want to query --if all the run PASS for test, the OverallStatus is PASS --If all the run Faile for a test, the OverallStatus is FAIL --If some of them pass and some of them fialed then OverallStaus is DEFECT

I want to get an output from the above table like

testId |numOfRun |OverallStatus

1 | 4 |Defect

2 | 1 |FAIL

3 | 1 |PASS

4 | 3 |PASS

5 | 3 |FAIL

6 | 3 |Defect

---

---

Answer 1

You can use conditional aggregation

select testId,
       numOfRun,
case when numOfRun = pass then 'pass'
    when numOfRun = fail then 'fail'
    else 'defect'
    end as OverallStatus 
from (
    select testId,
    count(*) numOfRun,
    sum(case when result = 0 then 1 else 0 end) as fail,
    sum(case when result = 1 then 1 else 0 end) as pass
    from table
    group by testId 
) t