You can use the aggregate FILTER clause to do it in a single query.
This gets three counts (fail, pass, warn) for every selected device on every day in the selected date range. A count of NULL for days without any appearance. 0 if the device appeared, but not with this status:
SELECT date, device_name
, fail_count, warning_count, pass_count
FROM (SELECT DISTINCT device_name FROM status_table) d -- all devices ①
CROSS JOIN (
SELECT generate_series(timestamp '2020-12-01'
, timestamp '2020-12-31'
, interval '1 day')::date
) t(date) -- all dates
LEFT JOIN (
SELECT date, device_name
, count(*) FILTER (WHERE overall_status = 'Fail') AS fail_count
, count(*) FILTER (WHERE overall_status = 'Warning') AS warning_count
, count(*) FILTER (WHERE overall_status = 'Pass') AS pass_count
FROM status_table
WHERE date >= '2020-12-01' -- same date range as above
AND date <= '2020-12-31'
GROUP BY 1, 2
) s USING (date, device_name)
ORDER BY 1, 2;
Basically, you CROSS JOIN all devices to all dates (Cartesian product), the append data where data can be found with a LEFT JOIN.
① Since you don't seem to have a device table (which you probably should), generate the full list on the fly. The above query with DISTINCT is good for few rows per device. Else, there are (much) faster techniques like:
WITH RECURSIVE cte AS (
(SELECT device_name FROM status_table ORDER BY 1 LIMIT 1)
UNION ALL
SELECT (SELECT device_name FROM status_table
WHERE device_name > t.device_name ORDER BY 1 LIMIT 1)
FROM cte
WHERE device_name IS NOT NULL
)
SELECT * FROM cte
WHERE device_name IS NOT NULL;
See:
The subquery s aggregates only rows from the given date range. It's strictly optional. You can also left-join to the underlying table directly, and then aggregate all. But this approach is typically (much) faster.
You can convert NULL to zero or vice versa with COALESCE / NULLIF.
Related:
For more flags, a crosstab() query might be faster. See:
About generating a date range:
Be aware that dates are defined by your current time zone setting if you operate with timestamp with time zone. See:
