avg of each day

Navigation
6

I need to get 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 I return 1 row with the general average of 7 days and not every day individually

    
asked by zhet 26.09.2018 в 02:06
source

2 answers

2

Try it like this: 

select fecha, AVG(temperatura) as temperatura,AVG(humedad) as humedad from mediciones 
where fecha between date_sub(now(),INTERVAL 1 WEEK) and now() and idvinedo=1
group by fecha
    
answered by 26.09.2018 / 02:11
source
2

Another possibility (in terms of how to filter for the current week) would be to write the query using YEARWEEK like this: 

SELECT
    fecha, 
    AVG(temperatura) temperatura,
    AVG(humedad) humedad
FROM mediciones 
WHERE 
    YEARWEEK(fecha) = YEARWEEK(CURDATE()) 
    AND idvinedo=1
GROUP BY fecha
ORDER BY fecha;

YEARWEEK has an interesting aspect, and is that would admit a second parameter mode which would serve to indicate for example that you want the week to start on Monday instead of Sunday: 

SELECT
    fecha, 
    AVG(temperatura) temperatura,
    AVG(humedad) humedad
FROM mediciones 
WHERE 
    YEARWEEK(fecha,1) = YEARWEEK(CURDATE(),1) 
    AND idvinedo=1
GROUP BY fecha
ORDER BY fecha;
    
answered by 26.09.2018 в 02:31
Pointers as parameters to functions in c Is it possible to use docker in windows without owning VT-x / AMD-v?