You are right.
Okay, I've updated the where clause with the date range. can I just update my IF statements to grab current month and not last 30/31 days?
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH),1,0))
SELECT COUNT(trailers.trailer_id) AS videosyear,
SUM(IF(trailers.date >= CURRENT_DATE,1,0)) AS videostoday,
SUM(IF(WEEKOFYEAR(trailers.date) = WEEKOFYEAR(CURRENT_DATE),1,0)) AS videosweek,
SUM(IF(MONTH(trailers.date)= MONTH(CURRENT_DATE),1,0)) AS videosmonth
WHERE trailers.date >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
AND trailers.date < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
+ INTERVAL 1 YEAR
Does that work? It certainly runs okay, and the WHERE clause makes sure there is no overlap on the MONTH WEEK and all that.
Thanks so much!