SQL query returns 0 for COUNT(*) if no rows

I just got started with SQL and joined this forum recently, thus I’m still lacking the knowledge. Please do be considerate if I’ve made any mistakes.

I intend to get the data from the past n days. For each day, the query will count the total rows and return the result. However, there are no records for some of the days, and the query does not return any result for the day. I need it to return a 0 if there are no records for that particular day. I’ve tried multiple queries and researched for quite a while but I can’t seem to get what I need.

This is my query:

select Date, COUNT(*) as TotalReservation
from Reservation
where Date between DateAdd(Day, -5, getdate()) and getDate()
group by Date

The Result I get:

| Date     | TotalReservation |
|----------|------------------|
|2021-06-04|        2         |
|2021-06-05|        3         |
|2021-06-06|        2         |

What I want:

| Date     | TotalReservation |
|----------|------------------|
|2021-06-04|        2         |
|2021-06-05|        3         |
|2021-06-06|        2         |
|2021-06-07|        0         |

I’ve tried ISNULL() and COALESCE() but they didn’t work either. Would appreciate it if anyone can enlighten me with this.

Answer

Description: You can use a recursive CTE to build your list of “dates” which you want to see. I hard-coded 5, as that was your example, you can use whatever works for you, even a variable. The LEFT JOIN will ensure that you get a record for every date in your range, and you’ll get 0 for those dates which don’t have data

Code Example:

WITH dateTable AS (
    SELECT 
         CAST(GETDATE() AS DATE )Date
    UNION ALL   
    SELECT 
         DATEADD(DAY,-1,dateTable.Date) Date
    FROM dateTable
    WHERE Date >= GETDATE() - 5 /*Enter your number of days to go back, here*/
)
SELECT 
     dateTable.Date
    ,COUNT(Reservation.Date)
FROM dateTable
LEFT OUTER JOIN Reservation ON dateTable.Date = Reservation.Date
GROUP by dateTable.Date
ORDER BY dateTable.Date;

Documentation: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15