GROUP BY two columns and return a third column values based on condition in SQL

Question

I have some data consisting of a date_time, job_name and status column. The jobs can be run once per day or multiple times per day at different moments and they result in a status which is either "success", "failed" or "disabled".

My data is looking something like this:

date_time	job_name	status
01/01/2020 07:30:30	job_1	success
01/01/2020 15:30:30	job_1	disabled
01/01/2020 18:30:30	job_1	failed
01/01/2020 08:30:30	job_2	success
01/01/2020 18:30:30	job_2	disabled
01/02/2020 15:30:30	job_1	success
01/02/2020 08:30:30	job_2	success
01/02/2020 18:30:30	job_2	success

I am trying to group these data by the date (note, not the date/time) and job_name. Per date (note, not the date/time) I'd like to return whether a job was successful or not.

If one of the runs (or the only run) of a job on a day is "failed", my grouped row must return "failed" regardless of the other "status" results that day.

If one of the runs (or the only run) of a job on a day is "disabled", and there are no "failed" runs of that job that day, my grouped row must return "disabled".

If all of the runs of a job on a day are "success", my grouped row must return "success".

Hence, the expected result of my example table would be:

date_time	job_name	status
01/01/2020	job_1	failed
01/01/2020	job_2	disabled
01/02/2020	job_1	success
01/02/2020	job_2	success

I have tried to attain this result using a GROUP BY statement on the date_time and job_name columns but I can't manage to implement the logic of when to return the "success", "failed" and "disabled" status.

Any help is appreciated!

Answer 1

Using conditional aggregation, we can try:

SELECT
    DATE(date_time) AS date_time,
    job_name,
    CASE WHEN COUNT(CASE WHEN status = 'failed' THEN 1 END) > 0
         THEN 'failed'
         WHEN COUNT(CASE WHEN status = 'disabled' THEN 1 END) > 0
         THEN 'disabled'
         WHEN COUNT(CASE WHEN status != 'success' THEN 1 END) = 0
         THEN 'success' END AS status
FROM yourTable
GROUP BY
    DATE(date_time),
    job_name
ORDER BY
    DATE(date_time),
    job_name;