How to display all date values as null in a date range of mysql?

Question

I have a table of booking and need to find the count of booking in a specific date range in MYSQL. But it is giving me only non empty values. I need to show the non existence date within range as NULL in my select query. Here is my query:

SELECT DATE_FORMAT(booking_created_on, '%b %e') AS dateValue , COUNT(booking_id) AS cnt FROM booking WHERE booking_created_on BETWEEN '2016/02/01' AND '2016/02/08' GROUP BY booking_created_on

My result from this query is :

And my expected result is:

Please help me in mysql query for getting the desired result.

Refer this http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — Piyush Gupta, Feb 15 '16 at 05:53
you want BETWEEN '2016/02/01' AND '2016/02/08' this date. i ask this because you didnt give the 01,02,08 date in expected result ? what you want ? — Vipin Jain, Feb 15 '16 at 05:59
Possible duplicate of [MySQL: Select All Dates In a Range Even If No Records Present](http://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present) — Mr. Engineer, Feb 15 '16 at 06:35

score 0 · Answer 1 · answered Feb 15 '16 at 06:47

Try This

SELECT DATE_FORMAT(selected_date, '%b %e'), cnt 
FROM(
        SELECT 
        str_to_date(booking_created_on, '%Y/%m/%d') AS selected_date , 
        COUNT(booking_id) AS cnt 
        FROM booking 
        WHERE booking_created_on BETWEEN '2016/02/01' AND '2016/02/08' 
        GROUP BY booking_created_on

        UNION          

        SELECT selected_date,0 AS cnt FROM 
        (SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
         (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
         (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
         (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
         (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
         (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
        where selected_date BETWEEN '2016-02-01' and '2016-02-08' AND selected_date NOT IN (
                SELECT 
                DISTINCT(str_to_date(booking_created_on, '%Y/%m/%d')) FROM booking 
                WHERE booking_created_on BETWEEN '2016/02/01' AND '2016/02/08' 
        )
) AS T1 
ORDER BY selected_date ;

In my query, first i get all date and count in your table and in second subquery i get all missing date which is not in your table between two dates. show result order by date. this is lengthy query so may be it will be some error so if any error, comment

can you create fidlere or plunker for this question. i have not data and your structure. after this i will give write query — Vipin Jain, Feb 15 '16 at 09:20

How to display all date values as null in a date range of mysql?

1 Answers1