Fill missing dates in mysql query range

Question

I have the following query:

select date(updated_at) as data, COUNT(id) as numar 
from `coupons`
where `user_id` = 5 and `won_by` != 0 and `updated_at` >= '2016-04-01'
group by DAY(updated_at), month(updated_at), year(updated_at)

and the result is this:

2016-04-01-   229
2016-04-03-   30
2016-04-04-   6
2016-04-07-   1
2016-04-08-   1
2016-04-10-   1

What can I do to receive something like this:

2016-04-01-   229
2016-04-02-   0
2016-04-03-   30
2016-04-04-   6
2016-04-05-   0
2016-04-06-   0
2016-04-07-   1
2016-04-08-   1
2016-04-10-   1

Why don't you GROUP BY date(updated_at)?

Paul Spiegel
– Paul Spiegel

2016-04-11 20:39:36 +00:00
Commented Apr 11, 2016 at 20:39 — Paul Spiegel
– Paul Spiegel, Commented Apr 11, 2016 at 20:39

Jacob Creed · Accepted Answer · 2016-04-11 20:49:40Z

2

The best way that I've found to do this is to simply create (and maintain) a secondary table with a single column, containing all of the dates that you care about. Something like:

CREATE TABLE date_join ( date date not null primary key );

Then insert records for each date in whatever way is convenient (by hand, if it's a one-off, as part of your daily process, via stored procedure, etc).

At that point, it's simply a left join of date_join and your initial query, with a CASE statement to translate NULLs to 0s:

SELECT dj.date, q.numar
  FROM      date_join dj
  LEFT JOIN (select date(updated_at) as date, COUNT(id) as numar 
               from `coupons`
              where `user_id` = 5 and `won_by` != 0 and `updated_at` >= '2016-04-01'
              group by DATE(updated_at)
            ) q
         ON dj.date = q.date
   ORDER BY dj.date;

answered Apr 11, 2016 at 20:49

Jacob Creed

5684 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Spiegel Over a year ago

A table with 100K dates (200+ years) will consume about 2.5MB in MySQL. No need to ever maintain it in your life :-)

Collectives™ on Stack Overflow

Fill missing dates in mysql query range

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related