I have a MySQL Table that has a datetime field and counts of fish that I query with PHP scripts to produce charts. steelhead charts The customer has suggested that a ten year average of count would be a nice addition to the charts. Is there a SQL statement that somehow uses AVG to produce a array of average count for each month day combination across the last ten years? Or is it better to manipulate this in PHP?
I guess the query wasn’t as tricky as I thought. I thought DISTINCT month day would be necessary but GROUP BY was the deal. This SQL query got me the results I want. I thought about using WHERE YEAR(chkDt) > YEAR(CURDATE())-11 AND YEAR(chkDT) = YEAR(CURDATE())-1 but decided to just fix the year range, because I might not know what year the script is being run. Now I need to implement this in the script.
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
YEAR (chkDt) > 2006
AND YEAR (chkDt) < 2018