mysql fill in missing dates for each specific value in column

Question

I am trying to count the number of events in the subchannel column of my l_events table between the dates 2013-10-01 and 2014-03-31 and group by specific subchannel, year and month.

Right now my results look like:

year    month   subchannel      count(*)
2013      10    creativemornings    1
2014       3    creativemornings    2
2013      11    founderinstitute    1

I would like my results to include rows for each specific subchannel where no events occurred occurred in that month. So something like:

year    month   subchannel      count(*)
2013      10    creativemornings    1
2013      11    creativemornings    0
2013      12    creativemornings    0
2014       1    creativemornings    0
2014       2    creativemornings    0
2014       3    creativemornings    2
2013      10    founderinstitute    0
2013      11    founderinstitute    1
2013      12    founderinstitute    0
2014       1    founderinstitute    0
2014       2    founderinstitute    0
2014       3    founderinstitute    0

I tried to join my query to my calendar table (which includes a datetime column of all relevent dates) but it didn't work. Any thoughts?

    SELECT  
        year(l.created) as year,
        month(l.created) as month,
        l.subchannel,
        count(*)

    FROM db.l_events l

    right JOIN db.calendar c 
        ON (date(l.created) = c.datefield)

    WHERE  
         l.created between '2013-10-01' and '2014-03-31' 

    group by
        l.subchannel,
        year(l.created), 
        month(l.created)
        ;

Ron Smith · Accepted Answer · 2014-04-07 20:27:17Z

1

This will do it. It creates a Cartesian product of your subchannel list and your calendar table and LEFT JOINs the l_events table to that result set.

SELECT  
    year(c.datefield) as year,
    month(c.datefield) as month,
    s.subchannel,
    count(l.created)
FROM db.calendar c
    CROSS JOIN (select distinct subchannel from l_events) s
    LEFT JOIN db.l_events l
        ON (date(l.created) = c.datefield)
        AND l.subchannel = s.subchannel
Where c.datefield between '2013-10-01' and '2014-03-31' 
group by
    s.subchannel,
    year(c.created), 
    month(.created)
order by
    s.subchannel,
    year(c.created), 
    month(.created)
    ;

edited Apr 7, 2014 at 20:27

answered Apr 7, 2014 at 20:21

Ron Smith

3,2661 gold badge15 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

mysql fill in missing dates for each specific value in column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related