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

Question

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.

What RDBMS are you using? Sql-Server, MySQL, Postgres, something else? — JNevill, Oct 12 '15 at 15:12
What exactly seems to be a problem? Why did you tag it with `coalesce`? Do you expect `NULL` values? — PM 77-1, Oct 12 '15 at 15:14

score 2 · Accepted Answer · answered Oct 12 '15 at 15:14

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)

score 0 · Answer 2 · answered Oct 12 '15 at 15:14

0

This is an aggregation query. Assuming fail only takes on two values (0 and 1):

select MFGName, Code,
       (case when max(fail) = 1 then 'FAIL' else 'PASS' end) as Result
from tests
group by MFGName, Code;

answered Oct 12 '15 at 15:14

Gordon Linoff

1,242,037
58
646
786

Moojjoo · Answer 3 · 2015-10-12T15:28:34.273

Joeldub here you go.

DECLARE @isFail AS INT

@isFail = (SELECT COUNT(FAIL) FROM FIRSTTABLE WHERE MFG NAME = 'ABC' AND FAIL = 1)

IF @isFail > 0
     BEGIN
        INSERT INTO SECONTABLE(with breaker) (Result) VALUES ('FAIL') WHERE WHERE MFG Name = 'ABC'
        ---- OR IF RECORD EXISTS
        UPDATE SECONDTABLE(with breaker) Result = 'FAIL' WHERE MFG Name = 'ABC'
     END
ELSE
     BEGIN
        INSERT INTO SECONTABLE(with breaker) (Result) VALUES ('PASS') WHERE WHERE MFG Name = 'ABC'
        ---- OR IF RECORD EXISTS
        UPDATE SECONDTABLE(with breaker) Result = 'PASS' WHERE MFG Name = 'ABC'
     END

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

3 Answers3