MySQL Query Problem with INTERVAL, need 0 if no data provided

i have the following statement:

SELECT
count(rs.rsc_id) as counter
FROM shots as rs
where rsc_rs_id = 345354
AND YEAR(rs.timestamp) = YEAR(DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
GROUP BY DATE_FORMAT(rs.timestamp,'%Y%m')

rs.timestamp is a unix timestamp

Output would be like for each row / month a numeric like '28'

It Works fine, but if i have inconsistent data, like only for the past three month (not for all six month), i get no return from my Database. I would like to ha开发者_C百科ve every time there is not data for this month, 0 returned...

any suggestion? i thought about some case statements, but this seems not so good...

thanks!!

For only 6 months, a date table seems unnecessary, although this looks complicated (it really isn't!)

SELECT DATE_FORMAT(N.PivotDate,'%Y%m'), count(rs.rsc_id) as counter
FROM (
   select ADDDATE(CURDATE(), INTERVAL N MONTH) PivotDate
   FROM (
       select 0 N union all
       select 1 union all
       select 2 union all
       select 3 union all
       select 4 union all
       select 5 union all
       select 6) N) N
LEFT JOIN shots as rs
    ON rsc_rs_id = 345354
    AND DATE_FORMAT(N.PivotDate,'%Y%m')=DATE_FORMAT(FROM_UNIXTIME(rs.timestamp),'%Y%m')
GROUP BY DATE_FORMAT(N.PivotDate,'%Y%m')

In such cases it's common to use a table of dates with all dates (e.g. from 1/1/1970 to 31/12/2999) and LEFT JOIN your data to that table.

See an example in the answer here: mysql joins tables creating missing dates

If you create a dates table you can use:

SELECT
DATE_FORMAT(d.date,'%Y%m') AS `month`, count(rs.rsc_id) AS `counter`
FROM dates d
LEFT JOIN shots as rs
   ON d.date = FROM_UNIXTIME(rs.timestamp)
   AND rs.rsc_rs_id = 345354
WHERE d.date > DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
AND d.date < CURDATE()
GROUP BY DATE_FORMAT(d.date,'%Y%m');