Replace No Result With Zero

Question

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.

Then you need to use another table as the base table for your query. — Sean Lange, Jan 19 '22 at 19:25
I really recommend using whitespace and line breaks. That single line of SQL is not easy to read; for you or others. — Thom A, Jan 19 '22 at 19:32
@AaronBertrand, the table is a large table with different values in the ```reason``` column. It just happen that the two dates in the example has ```data_count``` in the column. — Ope Baba, Jan 19 '22 at 20:03
@AaronBertrand, I updated the original question. Basically all I want is for the query to ALWAYS return something. For example in the updated query, there are no names ```Smith``` and ```Will``` in the table for those two dates specified in the query. So instead of not returning a value for them, I want ```0```to be returned as count. — Ope Baba, Jan 19 '22 at 22:00

LukStorms · Answer 1 · 2022-01-19T20:10:57.933

1

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;

edited Jan 19 '22 at 20:10

answered Jan 19 '22 at 19:51

LukStorms

28,916
5
31
45

Replace No Result With Zero

1 Answers1