How to return zero to result column if no values are not found in added_date in mysql query

Question

MySQL Query to get the added_date records in result:

SELECT added_date, count(added_date) AS result
FROM reports
WHERE added_date BETWEEN '2017-08-01' AND '2017-08-10'
GROUP BY added_date

Query result:

added_date  result
2017-08-01  20
2017-08-02  10
2017-08-04  22
2017-08-08  1

I want the result as:

    added_date  result
    2017-08-01  20
    2017-08-02  10
    2017-08-03   0
    2017-08-04  22
    2017-08-05   0
    2017-08-06   0
    2017-08-07   0
    2017-08-08   1

Possible duplicate of [MySQL how to fill missing dates in range?](https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — Riedsio, Aug 23 '17 at 18:04

score 0 · Answer 1 · edited Aug 23 '17 at 18:01

As shown in below code. You can create calendar table.Which provide us exact calendar as we required. Make entry into that calendar table.

DROP TABLE IF EXISTS time_dimension;

CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (db_date)

) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO time_dimension VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;

CALL fill_date_dimension('2000-01-01','2018-01-01');

Once that calendar table is created, you can write below query and it will resolve your issue.

SELECT added_date,count(added_date) as result 
FROM time_dimension TD
LEFT JOIN reports RP ON DATE(RP.added_date)=TD.DB_DATE
WHERE added_date BETWEEN '2017-08-01' AND '2017-08-10' group by 
GROUP BY DATE(DB_DATE);

How to return zero to result column if no values are not found in added_date in mysql query

1 Answers1