Reputation: 78
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 ?
Upvotes: 1
Views: 1523
Reputation: 147216
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
Upvotes: 5