Finding averages after every 60 minutes

[reddyadi]

HI,
I am having a weather data in which the temperature and othe parameters were recorded ar every 15 minutes interval but i want the average temp at every one hr(60 minutes) interval that ejmans the average temperature of frist 60 minutes then the average of the next 60 minutes.......
My data is ln this form
Time temperature humidity
0 20 6
15 22 2
30 15 4
45 26 10
60 30 11
75 15 8

i would be thanKful to anyone who can help me in writing a query in access.

I GOT TEH REPLY SAYING I CAN USE THIS QUERY BUT WITH THIS QUERY I AM ONLY ABLE TO GET THE DATA AT ECERY 60 MINUTES BUT NOT THE AVERAGE, I WANT THE AVERAGE OF THE TEMP AND HUMIDITY AFTER EVERY 60 MINUTES.

SELECT Table1.Time, Table1.Temp, Table1.Humid
FROM Table1
WHERE (((Table1.Time)<>0) AND (([Table1]![Time] Mod 60)=0));


if you wish to get the average only of the above query:




SELECT Avg([mod].Temp) AS AvgOfTemp, Avg([mod].Humid) AS AvgOfHumid
FROM [SELECT Table1.Time, Table1.Temp, Table1.Humid
FROM Table1
WHERE Table1.Time<>0 And Table1!Time Mod 60=0
]. AS mod;

 

[Jon K]

You can use an hour-list table to store the start time of each hour then build a Totals Query from the two tables.

See the query in the attached database.
.

 

[namliam]

select cint(time/60) Hours, avg(temperature), avg(Humidity)
from table1

Regards

 

[EMP]

select cint(time/60) Hours, avg(temperature), avg(Humidity)
from table1

??

Partly a select query, partly a totals query

 

[namliam]

Sorry *, offcourse it should be:

select cint(time/60) Hours, avg(temperature), avg(Humidity)
from table1
Group by cint(time/60)

Greetz

* I seem to be human after all

 

[EMP]

Based on the requirement specified in the General forum:
http://www.access-programmers.co.uk/forums/showthread.php?t=81552

i need the average of temp and humidity at after evry 60 minutes i.e the average of the 4 records at regular intervals.

I think Jon K has provided the correct solution.

 

[namliam]

Mine will do the same but is only 1 query... tho Jon's solution is more versotile mine will suffice....

Regards

PS. Not meaning to be a "mr. know-it-all"

PPS. reddyadi, you should not post 1 question double on the forum.... Just post it once and be patient....

 

[EMP]

From these 8 records for the first two hours of a day:

Time	Temperature	Humidity
  0	20	 	6
 15	20	 	6
 30	20	 	6
 45	20	 	6
 
 60	15	 	8
 75	15	 	8
 90	15	 	8
105	15	 	8

Jon's query returns the results:-

Hour	AvgOfTemperature	AvgOfHumidity
1		20			6
2		15			8

while your query, when changed to

select cint(time/60) AS Hours, avg(temperature), avg(Humidity)
from table1
Group by cint(time/60)

so as to avoid a syntax error, returns these results:-

Hours	Expr1001		Expr1002
0	20			6
1	16.6666666666667	7.33333333333333
2	15			8

An analysis of the grouping in your query:-
SELECT Time, cint(time/60) AS Hours
FROM table1;

shows:-

Time	Hours
0	0
15	0
30	0
45	1
60	1
75	1
90	2
105	2

As your query Groups by cint(time/60), obviously it returns the averages of the first three records, then the second three records, and then the last two records.

I don't see how your query meets the requirement of returning
the average of 4 records at regular intervals

Regards

 

[namliam]

Yes hmmm... The AS i forget every now and again (ORACLE contamination)
Also i should not have used CInt but simply Int.... That does work ...
Resulting in
select int(time/60) AS Hours, avg(temperature), avg(Humidity)
from table1
Group by int(time/60)