moving average by date in mysql

Question

I want to retrieve a moving average of a cosumes table. I have written the following query but it doesn't work. It retrieves higher averages for the first days. What am I doing wrong?

select c.fecha, count(r.cantidad), avg(r.cantidad)
from 
(select distinct fecha
from tb_consumos
where fecha > '2015-02-01') c, tb_consumos r
where datediff(c.fecha, r.fecha)<10
 group by c.fecha

score 0 · Answer 1 · answered Feb 26 '15 at 13:30

0

Try this:

select distinct c.fecha, count(r.cantidad), avg(r.cantidad)
from tb_consumos c, tb_consumos r
where datediff(c.fecha, r.fecha)<10 
and c.fecha > '2015-02-01'
group by c.fecha

answered Feb 26 '15 at 13:30

starko

1,150
11
26

This dindn't work for me. 'distinct c.fecha' needs to be done previous to the cartesian product of cxr – criptero Feb 26 '15 at 16:38

score 0 · Answer 2 · answered Feb 26 '15 at 16:36

I had an error comparing dates, now is working:

select c.fecha, count(r.cantidad), avg(r.cantidad)
from 
(select distinct fecha
from tb_consumos
where fecha > '2014-02-01') c, tb_consumos r
where abs(datediff(c.fecha, r.fecha))<10
 group by c.fecha

moving average by date in mysql

2 Answers2