How can I get a non-empty result set when using select, group by and count?
This is the current request:
SELECT status.ct_reconcile_status,
IFNULL(COUNT(status.ct_reconcile_status), 0) AS sch_change_status_num
FROM db_crewops_cm_action status
WHERE status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' )
AND status.updated_ts > UTC_TIMESTAMP() - INTERVAL 60 minute
GROUP BY status.ct_reconcile_status;
And the result I want to get looks like this:
SUCCESS 5
FAILED 9
and that's okay. The problem is that I can get a case where the number of results is 0 for both statuses, in which case I want:
SUCCESS 0
FAILED 0
and not an empty result set (which I am getting now). I have a general idea that I should either join the table on my own or use the WITH clause, but I don't know how to get started.
+3
source to share
1 answer
Try something like:
SELECT a.status AS ct_reconcile_status,
(IFNULL(b.sch_change_status_num,0) + a.num) AS sch_change_status_num
FROM
(SELECT 'SUCCESS' AS status, 0 AS num
UNION
SELECT 'FAILED', 0) a
LEFT JOIN
(SELECT status.ct_reconcile_status,
COUNT(status.ct_reconcile_status) AS sch_change_status_num
FROM db_crewops_cm_action status
WHERE status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' )
AND status.updated_ts > UTC_TIMESTAMP() - INTERVAL 60 minute
GROUP BY status.ct_reconcile_status) b
ON a.status = b.ct_reconcile_status
You can uninstall status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' ) AND
, but it might be slower.
+3
source to share