mysql single table group by removing zero elements

Question

I have a single table as below

| ID |        CREATED       |                TITLE           |
| 1  |  07/09/2015 14:02:48 |  Render farm problem           |
| 2  |  06/16/2015 09:34:20 | Render server THSSE12 crashing |
| 3  |  05/16/2015 09:44:38 |  Patch to LG4 switch port 25   |

I'd like to be able to count the occurrence of a key word in the TITLE field e.g. Render and format the result by year and month

| YEAR | MONTH | COUNT |
|2015  | 5     | 0     |
|2015  | 6     | 1     |
|2015  | 7     | 1     |

I have tried several inner joins but without any joy as the months with 0 counts do not show. This is where I'm at:-

 SELECT 
    CONCAT(YEAR(c.CREATED),MONTH(c.CREATED)) AS cdate,
    c.CREATED,
    COUNT(c.ID)
FROM 
    HD_TICKET AS c
LEFT JOIN 
    (SELECT
        CONCAT(YEAR(t.CREATED),MONTH(t.CREATED)) AS sdate,
        t.CREATED,
        COUNT(t.ID) AS tid
        FROM HD_TICKET t
        WHERE t.TITLE LIKE '%render%'
    ) AS t_count
ON c.CREATED = t_count.CREATED
GROUP BY YEAR(c.CREATED), MONTH(c.CREATED) 

I'd really appreciate any help!

Answer 1

This first generates all years/months values and then left join the desired data. All details inline. if you prefer, you can use the same trick on years. fiddle here

select  calendar.year_, calendar.month_, 
        coalesce(mydata.count_,0) as count_                    -- coalesce used to obtain 0 on missing data.
from
(   select y.year_, m.month_
    from
    (select distinct YEAR(CREATED) as year_ FROM hd_ticket) as y      -- all posible years
         ,                                                            -- comma here produces cross join
    (select 1 as month_ union all select 2                            -- all months 
     union all select 3 union all select 4
     union all select 5 union all select 6
     union all select 7 union all select 8
     union all select 9 union all select 10 
     union all select 11 union all select 12) as m
) as calendar                                                  -- a calendar: all years and all months
left join
(
    select count(*) as count_, year(CREATED) as year_, month(CREATED) as month_
    from HD_TICKET
    where TITLE like '%render%'
    group by year(CREATED), month(CREATED) 
) as mydata 
on calendar.year_ = mydata.year_ and calendar.month_ = mydata.month_
order by 1, 2