Fanny V Fanny V - 1 month ago 5
SQL Question

Group a column depend on the result

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

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