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

Question

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.

You need to generate the missing rows. Do you have a calendar table of some sort? — Gordon Linoff, Jun 08 '21 at 15:05
Hi, check this post https://stackoverflow.com/questions/54825778/how-to-get-missing-dates-with-0-value-in-sql-server — 1010, Jun 08 '21 at 15:08
@GordonLinoff I utilized a CTE to accomplish that in my answer, glad that we had the same train of thought — Eli, Jun 08 '21 at 15:59

score 2 · Accepted Answer · answered Jun 08 '21 at 15:58

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://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

Hi! Thanks for the detailed explanation! This solved my issue after a long time searching the whole web for answers. — Nigel Ang, Jun 09 '21 at 02:18

Andy3B · Answer 2 · 2021-06-08T15:53:11.853

1

Try this:

DECLARE @StartDate  date    = '2021-06-04'
    ,   @Days       int     = 5
;
DECLARE     @Reservation    TABLE ([Date] date, TotalReservation int)
INSERT INTO @Reservation
    VALUES  
            ('2021-06-04', 2)
        ,   ('2021-06-05', 3)
        ,   ('2021-06-06', 2)
;

DECLARE @DateTable  TABLE   (DDate date);
WHILE @Days > 0
    BEGIN
        INSERT INTO @DateTable VALUES (DATEADD(day, @Days-1, @StartDate))
        SET @Days = @Days -1
    END
;

SELECT  
            [Date]              =   D.DDate
        ,   TotalReservation    =   ISNULL((SELECT SUM(TotalReservation) FROM @Reservation WHERE D.DDate = [Date]), 0)
FROM        @DateTable      D
LEFT JOIN   @Reservation    R   ON D.DDate = R.[Date]
ORDER BY    [Date]

edited Jun 08 '21 at 15:53

answered Jun 08 '21 at 15:38

Andy3B

444
2
6

Hi! I appreciate the help a lot. I have a question though. For the first 3 rows in the Result, the values in Reservation column are counted with count(*) and they are not default values. So what should I do instead of inserting the values into @Reservation? – Nigel Ang Jun 09 '21 at 02:13
1

First, you don't want to COUNT. In order to get total of Reservations, you have to SUM. Second, the @Reservation Table I use is just an sample. Use your real Reservation Table instead without the @. Third, i gave you this example just in case you have problems with MAX recursion option; but the recursive version is also good. – Andy3B Jun 09 '21 at 13:21

score 0 · Answer 3 · answered Jun 08 '21 at 16:57

With recursion would be like:

DECLARE @StartDate  date    = '2021-06-04'
    ,   @Days       int     = 5
;
DECLARE     @Reservation    TABLE ([Date] date, TotalReservation int)
INSERT INTO @Reservation
    VALUES  
            ('2021-06-04', 2)
        ,   ('2021-06-05', 3)
        ,   ('2021-06-06', 2)
;

WITH DateTable AS (
    SELECT 
         DDate = CONVERT(date, @StartDate)
    UNION ALL   
    SELECT 
         DDate = DATEADD(day, 1, D.DDate)
    FROM DateTable D
    WHERE 
            DDate <= DATEADD(day, @Days-2, @StartDate)
)

SELECT  
            [Date]              =   D.DDate
        ,   TotalReservation    =   ISNULL((SELECT SUM(TotalReservation) FROM @Reservation WHERE D.DDate = [Date]), 0)
FROM        DateTable       D
LEFT JOIN   @Reservation    R   ON D.DDate = R.[Date]
ORDER BY    [Date]

score 0 · Answer 4 · 2021-06-09T04:50:27.373

DECLARE @Reservation TABLE (ID int PRIMARY KEY IDENTITY(1,1), [Date] date)

DECLARE @Date smalldatetime = '20210604'

INSERT INTO @Reservation VALUES  (@Date), (@Date), 
    (@Date + 1), (@Date + 1), (@Date + 1), 
    (@Date + 2), (@Date + 2);


WITH Dates_CTE (Date) AS (
    SELECT @Date 
    UNION ALL
    SELECT DATEADD(DAY, 1, Date) 
    FROM Dates_CTE
    WHERE Date < @Date + 4
)
SELECT D.Date, COUNT(R.ID)
FROM Dates_CTE D
LEFT JOIN @Reservation R ON D.Date = R.Date
GROUP BY D.Date
ORDER BY D.Date

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

4 Answers4