CODEWITHSUNDEEP
sqlsql-server-2012sql-server-2016
Rushang
Rushang

Reputation: 175

Check data condition with multiple row and get only single output value

Check data condition with multiple row and get only single output value

Status
SUCCESSFULL
SUCCESSFULL
SUCCESSFULL
SUCCESSFULL

Then need Output "SUCCESSFULL"

IF any record have "Failed" status then output will be "Failed" as below

Status
SUCCESSFULL
SUCCESSFULL
FAILED
SUCCESSFULL

Then need Output "FAILED"

Upvotes: 0

Views: 42

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Are you merely looking for this:

select min(status) from mytable;

('FAILED' comes before 'SUCCESSFULL' in the alphabet, so with MIN you get 'FAILED' when at least one row is 'FAILED' and 'SUCCESSFULL' only if all rows are 'SUCCESSFULL'.)

Upvotes: 1

Ajeet Verma
Ajeet Verma

Reputation: 1123

SELECT DISTINCT Status 
   FROM Your_table
      ORDER BY status

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

You can use aggregation functions count() and sum() to achieve this.

select 
    case when 
        sum(case when status = 'SUCCESSFULL' then 1 else 0 end)  = count(1) 
    then 'SUCCESSFULL' 
    else 'FAILED'
    end
from tableA

Upvotes: 0

Related Questions