Calculating Average.

Hello.
I have a form to input 'water meter' readings in 'units'. I take a reading at random intervals and a chart is generated. All good.
But I want to add to the chart the average per day usage (avg) since the previous reading (not avg for all readings).

index date reading avg
1 2012-1-19 1000 ------
2 2012-2-14 2000 38
3 2012-3-30 2000 22
4 2012-4-10 3000 90

For example above, index 2 -- 1000 units recorded / 26 days since previous reading = 38 avg (units per day)

How can I work this out with MySql. Thanks for trying anyone.

Jamie.

 

try this.

SELECT AVERAGE(reading) as AVG FROM table_name GROUP BY DAY(date), MONTH(date)

 

Thanks igunz but that's not what I mean. I need the average per day since the last reading. I'm trying to get the table listed above.

 

Can you please post the table structure here, I mean create table statement/script, few lines of data and desired result. Thanks

 

OK, I made an example, because you didn't post original table structure /I saw you online.../ - hope I understood the question right:

DROP TABLE IF EXISTS testing;

CREATE TABLE testing(`index` INT AUTO_INCREMENT PRIMARY KEY,
`date` DATE,
reading INT,
`avg` INT);
INSERT INTO testing VALUES(1,'2012-01-19',1000,26),
(2,'2012-02-14',2000,38),
(3,'2012-03-30',2000,22),
(4,'2012-04-10',3000,90);

Code (markup):

IMHO will be better if you don't use reserved words as column names.

(SELECT `index`,
`date` AS CURRENTDATE,
reading,
0 AS units_recorded_since_previous_reading,
0 AS days_since_previous_reading,
`avg` FROM testing ORDER BY `date` ASC LIMIT 1)
UNION
(SELECT `index`,
`date` AS CURRENTDATE,
reading,
(reading-(SELECT reading FROM testing 
WHERE `date`<CURRENTDATE 
ORDER BY `date` DESC LIMIT 1)),
DATEDIFF(`date`,
(SELECT `date` FROM testing 
WHERE `date`<CURRENTDATE 
ORDER BY `date` DESC LIMIT 1)),
`avg` FROM testing
WHERE `date`!=(SELECT `date` FROM testing ORDER BY `date` ASC LIMIT 1)
ORDER BY `date` ASC
);

Code (markup):

Edit: Added order by `date` on the second part of union.

 

Thanks very much koko5, but it doesn't calculate an average per day reading.
avg needs to be worked out dynamically, it isn't a column in the table.
I almost cracked it. This code works where index is continuous:

SELECT testing.index, testing.reading, testing.date, ((testing.reading - t2.reading) / DATEDIFF(testing.date, t2.date))as avg
FROM testing
LEFT JOIN testing AS t2 ON t2.index = testing.index - 1

BUT I need to add a new column 'groupid' to enable readings for other parameters. eg. groupid 1 is a 'water' reading. groupid 2 is a 'gas' reading.
when I build my chart I will specify which groupid I want to build.

So the above works for 1 groupid, but not for multiples.
My table is:
reading __date___ index_ groupid
1000__ 19-1-2012__ 1__ 1
2000__ 14-2-2012__ 2__ 1
3000__ 30-3-2012__ 3__ 1
4000__ 10-4-2012__ 4__ 1
20000__ 25-1-2012__ 5__ 2
30000__ 09-2-2012__ 6__ 2
40000__ 12-3-2012__ 7__ 2
5000 16-5-2012__ 8__ 1

Any ideas? Thanks again.

 

Edit: Post deleted-query returns wrong result... I'll think on it Jamie T, maybe to get index-1 we must join the table itself again as t3 with required conditions.

Regards

 

Finally...

your query:

SELECT testing.index, testing.reading,  testing.`date`,  ((testing.reading - t2.reading) / DATEDIFF(testing.`date`, t2.`date`))as `avg`
 FROM testing 
 LEFT JOIN testing AS t2 ON t2.index = testing.index - 1 ;

Code (markup):

can be rebuild as :

SELECT testing.`index`,
testing.reading,
testing.`date`,
ROUND(IFNULL(((testing.reading - (SELECT reading FROM testing AS t1 WHERE t1.`date`<testing.`date` ORDER BY t1.`date` DESC LIMIT 1)) / IF(DATEDIFF(testing.`date`, (SELECT `date` FROM testing AS t1 WHERE t1.`date`<testing.`date` ORDER BY t1.`date` DESC LIMIT 1))=0,1,DATEDIFF(testing.`date`, (SELECT `date` FROM testing AS t1 WHERE t1.`date`<testing.`date` ORDER BY t1.`date` DESC LIMIT 1)))),0),2) AS `average_per_day`
FROM testing
ORDER BY `date` ASC;

Code (markup):

Note, that in your code 3,'2012-03-30',2000,22 (avg=22 here, when we enter avg manually for the example), but your query returns zero here, as the same as mine-seems more correct, but depends on how and which average-entire period or time intervals we are looking for.
Will be better if we ignore id/index /resp. index-1/ here and get avg for time interval as you can see in my code.
Dependent subqueries you can rebuild as joins if you wish for performance.
The above takes care to prevent division by zero when timediff=0.

Here is the entire example-you can see what happens after update on `index` to make it non-continuous :

Hope it helps

 

Thanks Koko5, sorry for the delay.
It's not really working for me. My test table has 20000 entries and it takes too long to process.
I've come to the conclusion my best way will be to export into a temp table, calculate, then update origional table with averages (avg).

INSERT INTO watertemp (event_id, date, reading) SELECT event_id, date, reading FROM water where readingis not null and avg is null

watertemp auto assignes a row id , then:

update water(avg) set
(SELECT ((watertemp.reading- t2.reading) / DATEDIFF(watertemp.date, t2.date)) as avg
FROM watertemp
LEFT JOIN watertemp AS t2 ON t2.id = watertemp.id- 1)
where watertemp.event_id = water.event_id

As you can probably tell, the update does not work. It is ok until I put the 'where' clause at the bottom. Can anyone put this update query right for me please?

Thanks again koko5, I appreciate the time you've spent looking at my problem so far.

Jamie.