I need to obtain the average of each day, in a range of one week, what I need is for the query to return 7 rows where each row is 1 day of the week
I have the structure of the table is the following:
create table mediciones(
id int not null primary key AUTO_INCREMENT,
temperatura float not null,
humedad float not null,
idvinedo int not null,
fecha date not null,
FOREIGN KEY (idvinedo) REFERENCES vinedo(id));
and the query I have is:
select AVG(temperature) as temperature,AVG(humidity) as humidity from measurements where date between date_sub(now(),INTERVAL 1 WEEK) and now() and idvinedo=1
The problem is that it returns 1 row with the general average of the 7 days and not each day individually
Another possibility (as for the way to filter by the current week) would be to write the query using
YEARWEEK
like this:YEARWEEK
It has an interesting aspect, and it is that it would admit a second parametermode
which would be used to indicate, for example, that you want the week to start on Monday instead of Sunday:Try it like this: