Group a column depend on the result

Question

I've one basic question : I tried to create a query in SQL :

Select Date, Status From Table

My result is, for example ,

2016-06-07   FAILED
2016-06-07   FINISH OK 
2016-07-07   FINISH OK
2016-07-07   FINISH OK

Is it possible to tell If the status is 'FAILED' from the 2016-06-07, print just one line with the failed status. If it's just finish ok, print finish ok. I would like this result :

2016-06-07   FAILED
2016-07-07   FINISH OK

Thanks,

EDIT: I have just 2 status : FAILED and FINISH OK

more and more dates and results :

2016-06-07   FAILED
2016-06-07   FINISH OK
2016-06-07   FINISH OK
2016-06-07   FINISH OK
2016-06-07   FINISH OK

 2016-07-07   FINISH OK
 2016-07-07   FINISH OK
 2016-07-07   FINISH OK
 2016-07-07   FINISH OK
 2016-07-07   FINISH OK

2016-07-08    FINISH OK
2016-07-08    FINISH OK
2016-07-08    FINISH OK
2016-07-08    FAILED
2016-07-08    FINISH OK

I would like to have:

2016-06-07   FAILED
2016-07-07   FINISH OK
2016-07-08   FAILED

If I have one FAILED status in a date, I would like to print 'FAILED', but if I do not have any 'FAILED' status, I would print 'FINISH OK', Is it better?

Answer 1

use a CASE with conditional agregation.

The first COUNT is just to show you how work. This will count the number of fails on each date group.

Then the following CASE check if there are 1 or more fail

 SELECT Date,
        COUNT(CASE WHEN Status = 'FAILED' THEN 1 END) as total_fails,           
        CASE WHEN Count(CASE WHEN Status = 'FAILED' THEN 1 END) > 0 
             THEN 'FAILED'
             ELSE 'FINISH OK'
        END as Status 
FROM Table
GROUP BY Date