Average count per date across 10 years from MySQL Table


#1

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?


#2

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.

SELECT
MONTH (chkDt) AS MONTH,
DAY (chkDt) AS DAY,
AVG(WildSthd)
FROM
tblFishPassLyle
WHERE
YEAR (chkDt) > 2006
AND YEAR (chkDt) < 2018
GROUP BY
MONTH(chkDt),
DAY (chkDt)