AVG(value) grouped by user-defined intervals

Mucello

I have a table with daily water values. I'm trying to calculate percentiles, which I'm doing with a PHP class. But before I can do that, I must get the average water values for 1,7,14 days prior to each date in the table.

The query I'm using does not work as I thought it would... here it is:

SELECT year( ob ) as yearob, dayofyear( ob ) as dayofyearob , AVG( dv_va ) as avgwater
FROM water WHERE ob >= DATE_SUB( ob , INTERVAL 7 DAY ) AND ob <= ob
GROUP BY dayofyearob , yearob

... ob = YYYY-MM-DD
... dv_va = numeric value that I'm interested in averaging

The above query returns only the last record for each interval of time rather than the average for those intervals. I want the average for each 7 day interval of ob

Any ideas?

Roger_Ramjet

Too tired to try and code this but I'd look at joining the table to itself with the where clause on one side. That should give you each day joined to the preceding 7 day records which should allow you to group the group.

Mucello

Roger Ramjet wrote:
Too tired to try and code this but I'd look at joining the table to itself with the where clause on one side. That should give you each day joined to the preceding 7 day records which should allow you to group the group.

Thanks for your comment. I kind of follow what you're saying but don't totally grasp it. Would you mind giving me an example please?