Search code examples
sqlpostgresqlgroup-byhaving

Postgres - Select days with FAILURE status only

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
Solution

  • 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'