Search code examples
sqlcoalesceaggregates

Sql query to compare the result between multiple records and provide a result

I need to write a query that will result with a "fail" condition when one test fails. Testing results are listed in the Fail and Pass columns. One manufacturer product can have many tests. My Data is like this: 

MFG Name  | Code | Breaker | Fail | Pass
-----------------------------------------
ABC           R      2        0       1
ABC           R      1        1       0

Regardless of the Breaker value if one test fails, the entire batch fails. The result I was looking for is as follows:

  MFG Name  | Code | Result
-------------------------------------------
ABC             R     FAIL

Any help or insight you can give would be appreciated.

Solution

  • Try:

    select      mfg_name,
            code,
            case when sum(fail) > 0 then 'FAIL' else 'PASS' end as result
from        tbl
group by    mfg_name,
            code

    This assumes you want to show combinations of (mfg_name and code pairs with no fails as a 'PASS', for only fails you would add having sum(fail) > 0)