Query to return rows where count(*) is zero

Question

I am trying to write a SQL Server query which should return the zero count record as well. I know that aggregate functions ignore NULL values.

SELECT
    DAY(job_start_date) as day,
    count(app_id) as execution
FROM job_application_details
WHERE
    job_start_date >= DATEADD(day, -30, GETDATE())
    and job_start_date <= getdate()
    and app_id = 51
group by DAY(job_start_date);

Above query returns the result like below:

       | day  | execution
-------+------+--------------
    1  |  9   |  1
    2  |  12  |  1
    3  |  15  |  1
    4  |  30  |  1

Since for the other days, there are no records present in the table so it doesn't return it with 0 count. So, I want to write a query which returns the records of 30 days including the ones having 0 executions.

Please help to resolve this. Thanks in advance.

As per the question guide, please DO NOT post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Apr 29 '21 at 02:43
This is a common problem, you need a calendar table to join on. — Dale K, Apr 29 '21 at 02:43

score 0 · Answer 1 · answered Apr 29 '21 at 04:16

The issue here is that you need a table that contains the full set of dates you expect, a calendar, so you can carry out a LEFT JOIN. Then you can write something like this:

SELECT c.date, 
    SUM(IIF(d.job_start_date IS NULL, 0, 1)) AS execution
FROM calendar c
LEFT JOIN job_application_details d
    ON c.date = d.job_start_date 
WHERE c.date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()
    AND d.app_id = 51
GROUP BY c.date

If you don't have a calendar table already being loaded onto your database, this post outlines some easy ways to create one.

score 0 · Answer 2 · answered Apr 29 '21 at 13:05

Assuming your table has data for every day -- just not for app = 51, you can use conditional aggregation:

SELECT DAY(job_start_date) as day,
       SUM(CASE WHEN appid = 51 THEN 1 ELSE 0 END) as execution
FROM job_application_details
WHERE job_start_date >= DATEADD(day, -30, GETDATE()) AND
      job_start_date <= getdate()
GROUP BY DAY(job_start_date);

Otherwise you need a calendar table, tally table, or to generate the dates using a method such as a recursive CTE.

Query to return rows where count(*) is zero

2 Answers2