MYSQL fills the group with & ldquo; Gaps & rdquo;

advertisements

I´m trying to fill the gaps after using group by using an aux table, can you help?

aux table to deal with days with no orders

date        quantity
2014-01-01  0
2014-01-02  0
2014-01-03  0
2014-01-04  0
2014-01-05  0
2014-01-06  0
2014-01-07  0

group by result from "orders" table

date        quantity
2014-01-01  7
2014-01-02  1
2014-01-04  2
2014-01-05  3

desired result joining "orders" table with "aux table"

date        quantity
2014-01-01  7
2014-01-02  1
2014-01-03  0
2014-01-04  2
2014-01-05  3
2014-01-06  0
2014-01-07  0


Without knowing how you create your group by result table, what you're looking for in an outer join, perhaps with coalesce. Something like this:

select distinct a.date, coalesce(b.quantity,0) quantity
from aux a
    left join yourgroupbyresults b on a.date = b.date

Please note, you may or may not need distinct -- depends on your data.


Edit, given your comments, this should work:

select a.date, count(b.date_sent)
from aux a
    left join orders b on a.date = date_format(b.date_sent, '%Y-%m-%d')
group by a.date