MySQL populate empty date values

Question

I am running some query's against our incident logging database for our individual customers. What I want to return is all the month names from 2012 and a count of how many open incidents there have been. This works fine for a customer who has been with us for over 12 months but not for a customer who has been with us for a shorter period of time.

The query I have is this:

    SELECT DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y') as 'Year',
    DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%M') as 'Month',
    count(wo.workorderid) as 'Total Logged'
    FROM workorder_threaded wot
    inner join workorder wo
    on wo.workorderid = wot.workorderid
    and wo.siteid = 4806
    and DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y') <> '2011'
    where wot.workorderid = wot.thd_woid
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y%m')

The output I get is this:

    Year    Month       Total Logged
    2012    August      3
    2012    September   356
    2012    October     212
    2012    November    120

I need however the following:

    Year      Month       Total Logged
    2012      January     0 
    2012      February    0
    2012      March       0
    2012      April       0
    2012      May         0
    2012      June        0
    2012      July        0 
    2012      August      3
    2012      September   356
    2012      October     212
    2012      November    120
    2012      December    0

It doesn't have to be limited by year i.e. the call logging DB has data in this from 2011 so the query can be modified to filter the year range.

I know that this is not populating because the data does not exist and I have seen ways of creating a numbers \ date table and referencing this to populate the date but am struggling to do this. I have also changed around joins and where conditions to no avail.

Thanks in advance.

Answer 1

You need to use LEFT JOIN here