Postgres - Select days with FAILURE status only

user_11368290 0
Viewed 30

I have table with multiple result per days for particular project_name (SUCCESS, FAILURE, ABORTED, UNSTABLE). I would like to select only days where only FAILURE status occurs - nothing else. There are some days with multiple status (after FAILURE it can be solved and get status with different time and set to SUCCESS). How can I select only days with FAILURE status?

my query:

SELECT
    date_trunc('day', time) AS group_day,
    build_result,
    project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY date_trunc('day', time), project_name, build_result
having group-by postgresql sql
2 Answers

Use aggregation and having:

SELECT date_trunc('day', time) AS group_day, project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY date_trunc('day', time), project_name
HAVING MIN(build_result) = MAX(build_result) AND
       MIN(build_result) = 'FAILURE'
user_11440350

You can group by group_day and project_name and use string_agg() in the HAVING clause to set the condition that the only value of build_result is 'FAILURE':

SELECT date_trunc('day', time) AS group_day,
       project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY group_day, project_name
HAVING string_agg(DISTINCT build_result, ',') = 'FAILURE'
user_104988280
Related