Mysql – How to select the total count of records over the past week, including days which have no records in MySQL

countdataMySQLqueryselect

Essentially, I need a query which can output something like:

+------------+-------+
| date       | count |
+------------+-------+
| 2020-09-30 | 10    |
| 2020-09-29 | 3     |
| 2020-09-28 | 0     |
| 2020-09-27 | 6     |
| 2020-09-26 | 0     |
| 2020-09-25 | 5     |
| 2020-09-24 | 3     |
+------------+-------+

I know other questions ask something similar but none of the ones I have seen output 0 anything if the count is 0. They do this instead:

+------------+-------+
| date       | count |
+------------+-------+
| 2020-09-30 | 10    |
| 2020-09-29 | 3     |
| 2020-09-27 | 6     |
| 2020-09-25 | 5     |
| 2020-09-24 | 3     |
+------------+-------+

Best Answer

The most straightforward approach is to use a date table with all possible dates, then OUTER APPLY to get your hit count for each date, where appropriate.

Something like this:

SELECT  d.Date
        ,ISNULL(hc.HitCount, 0) AS HitCount
FROM    dbo.Date AS d
        OUTER APPLY (
                        SELECT  COUNT(1) AS HitCount
                        FROM    Table AS t
                        WHERE   1 = 1
                                AND t.Date = d.Date
                    ) AS hc
WHERE   1 = 1
        AND d.Date BETWEEN @StartDate AND @EndDate;

Here's a script to build and fill your date table:

CREATE PROC [dbo].[usp_GrowDateDimension]
(
    @Years TINYINT NULL = 25
)
AS
DECLARE @MaxDate DATE;

IF NOT EXISTS (
                  SELECT    1
                  FROM      sys.objects AS o
                  WHERE     o.name = 'Date'
              )
BEGIN
    CREATE TABLE dbo.Date
    (
        DateKey               INT          NOT NULL
        ,FullDateAlternateKey DATE         NOT NULL
        ,DayNumberOfWeek      TINYINT      NOT NULL
        ,EnglishDayNameOfWeek NVARCHAR(10) NOT NULL
        ,DayNumberOfMonth     TINYINT      NOT NULL
        ,DayNumberOfYear      SMALLINT     NOT NULL
        ,WeekNumberOfYear     TINYINT      NOT NULL
        ,EnglishMonthName     NVARCHAR(10) NOT NULL
        ,MonthNumberOfYear    TINYINT      NOT NULL
        ,CalendarQuarter      TINYINT      NOT NULL
        ,CalendarYear         SMALLINT     NOT NULL
        ,CalendarSemester     TINYINT      NOT NULL
        ,Yesterday            AS (CASE
                                      WHEN FullDateAlternateKey = CONVERT(DATE, DATEADD(DAY, (-1), GETDATE())) THEN (1)
                                      ELSE (0)
                                  END
                                 )
    );
END;

/* Get ceiling of Date dimension */
SELECT  @MaxDate = MAX(d.FullDateAlternateKey)
FROM    dbo.Date AS d
WHERE   d.FullDateAlternateKey <> '2999-12-31';

/* Set Start/Stop points based on ceiling */
DECLARE @CurrentDate DATE = DATEADD(DAY, 1, ISNULL(@MaxDate, '2000-12-31'));
DECLARE @EndDate DATE = DATEADD(YEAR, @Years, @CurrentDate);

/* Insert Rows */
WHILE @CurrentDate < @EndDate
BEGIN
    INSERT INTO dbo.Date
    (
        DateKey
        ,FullDateAlternateKey
        ,DayNumberOfWeek
        ,EnglishDayNameOfWeek
        ,DayNumberOfMonth
        ,DayNumberOfYear
        ,WeekNumberOfYear
        ,EnglishMonthName
        ,MonthNumberOfYear
        ,CalendarQuarter
        ,CalendarYear
        ,CalendarSemester
    )
    SELECT  YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) AS DateKey
            ,@CurrentDate AS FullDateAlternateKey
            ,DATEPART(dw, @CurrentDate) AS DayNumberOfWeek
            ,DATENAME(dw, @CurrentDate) AS EnglishDayNameOfWeek
            ,DAY(@CurrentDate) AS DayNumberOfMonth
            ,DATENAME(dy, @CurrentDate) AS DayNumberOfYear
            ,DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
            ,DATENAME(mm, @CurrentDate) AS EnglishMonthName
            ,MONTH(@CurrentDate) AS MonthNumberOfYear
            ,DATEPART(q, @CurrentDate) AS CalendarQuarter
            ,YEAR(@CurrentDate) AS CalendarYear
            ,((DATEPART(QUARTER, @CurrentDate) - 1) / 2) + 1 AS CalendarSemester
    WHERE   NOT EXISTS  (
                            SELECT  1
                            FROM    dbo.Date AS d
                            WHERE   d.FullDateAlternateKey = @CurrentDate
                        );

    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;
Related Question