CODEWITHSUNDEEP
mysql
Lukas
Lukas

Reputation: 510

MySQL query daily avg value over years for the same day

I've got a MySQL database filled with weather data, e.g. mean temperature value for every day. I would like query for the average of these values for every day the last five years.

for example: 

2019-06-04 20.04  
2018-06-04 18.42  
2017-06-04 19.21  
2016-06-04 21.22  
2015-06-04 17.19

query result should be: 19.216

For now I am able to get the avg for a specific day for the last years:

select date, avg(ta) from weatherdata where date like "20%-06-04";

But I am searching for an option to get the avg value for every day in a single query if possible.

Upvotes: 0

Views: 32

Answers (1)

Barmar
Barmar

Reputation: 781004

Use GROUP BY.

SELECT MONTH(date) AS month, DAY(date) AS day, AVG(ta)
FROM weatherdata
GROUP BY month, day
ORDER BY month, day

Upvotes: 1

Related Questions