Replace No Result With Zero
I need help with this query. I have a table that I'm running the below query on.
SELECT COALESCE(NULLIF(reason, ''), reason) AS reason,
COALESCE(NULLIF(count, ''), '0') AS count,
COALESCE(NULLIF(count_date, ''), count_date) AS count_date
from (select 'data_count' AS reason, count(*) as count, count_date
from temp_table
where count_date in ('01/02/2022' , '02/02/2022')
group by count_date
UNION ALL
select 'smith_call' AS reason, count(*) as count, count_date
from temp_table
where count_date in ('01/02/2022' , '02/02/2022') and name = 'Smith'
group by count_date
UNION ALL
select 'will_call' AS reason, count(*) as count, count_date
from temp_table
where count_date in ('01/02/2022' , '02/02/2022') and name = 'Will'
group by count_date);
I have an empty table returned as show below
+-------------+-------------+---------------+
|reason |count |count_date |
+-------------+-------------+---------------+
| data_count |20 |01/02/2022 |
+-------------+-------------+---------------+
However, I want the result to look like this.
+-------------+-------------+---------------+
|reason |count |count_date |
+-------------+-------------+---------------+
| data_count |20 |01/02/2022 |
+-------------+-------------+---------------+
| data_count |0 |02/02/2022 |
+-------------+-------------+---------------+
| smith_call |0 |01/02/2022 |
+-------------+-------------+---------------+
| smith_call |0 |02/02/2022 |
+-------------+-------------+---------------+
| will_call |0 |01/02/2022 |
+-------------+-------------+---------------+
| will_call |0 |02/02/2022 |
+-------------+-------------+---------------+
For date where there are no records, instead of completely ommiting in the result, I want it to have '0' as count.
A possible solution is to left join on values.
Or to a Calendar reference table.
SELECT
'data_count' AS reason
, count(t.count_date) as [count]
, v.count_date
FROM (VALUES
(CAST('2022-02-01' AS DATE)),
('2022-02-02')
) v(count_date)
LEFT JOIN temp_table t
ON t.count_date = v.count_date
GROUP BY v.count_date;