calculate pass and fail count in sql/oracle

i have database table in oracle like,

ID      status  cycle

T051    Passed   s1

T051    Failed   s1

T061    Failed   s1

T061    Failed   s1

T051    Failed   s2

i want output like this

ID   cycle pass   fail

T051   s1   1      0

T061   s1   0      1

T051   s2   0      1

Logic :-> if respective id&cycle any status is passed then its pass count 1 and if respective id&cycle all status is failed then fail count 1

can Anyone help here ?

Answer

You can use MIN and MAX aggregation functions to perform the equivalent of boolean logic for this query, using MAX to test if any status value is Passed, and MIN to check if any status value is not Failed:

SELECT ID, cycle,
       MAX(CASE WHEN status = 'Passed' THEN 1 ELSE 0 END) AS pass,
       MIN(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS fail
FROM data
GROUP BY ID, cycle
ORDER BY ID, cycle

Output:

ID      CYCLE   PASS    FAIL
T051    s1      1       0
T051    s2      0       1
T061    s1      0       1

Demo on dbfiddle