Averaging values taken every 30 seconds into 5 minute interval

Averaging values taken every 30 seconds into 5 minute interval

Post by TWFyayBSb3 » Sun, 27 Jan 2008 22:28:01



Thanks John, Amy and Marshall for your quick replies and helping with this,
it really is appreciated.

I tried the query John provided without the WHERE clause and it worked for
the most part except for the fiirst row returned. There does not appear to be
a 5 minute period between rows 1 and 2 but from there on all appears fine.

Below I have shown the original table, with some random entries I used for
testing, and the results returned by the query. Do you have any ideas why the
frst row does not appear to be right? Also, is it possible to get the query
to return values starting at the hour - i.e. 01:00, 01:05, 01:10 etc..?

Original table "DataRate":

Time_Stamp Throughput
01/01/2007 01:01:00 542520
01/01/2007 01:01:05 252520
01/01/2007 01:01:05 5432525
01/01/2007 01:02:05 52455
01/01/2007 01:02:05 6536361
01/01/2007 01:02:05 65656
01/01/2007 01:03:05 767463634
01/01/2007 01:03:05 767335
01/01/2007 01:03:05 5463633
01/01/2007 01:04:05 36343643
01/01/2007 01:04:05 3636374
01/01/2007 01:04:05 6363636
01/01/2007 01:05:00 667746
01/01/2007 01:05:05 679846
01/01/2007 01:05:05 7847746
01/01/2007 01:06:05 89785746
01/01/2007 01:06:05 696546
01/01/2007 01:06:05 658658436
01/01/2007 01:07:05 35352
01/01/2007 01:07:05 77366
01/01/2007 01:07:05 67547754
01/01/2007 01:08:05 63636677
01/01/2007 01:08:05 657474
01/01/2007 01:08:05 43254358
01/01/2007 01:09:05 767856
01/01/2007 01:09:05 4747746
01/01/2007 01:10:00 9986964
01/01/2007 01:10:05 8574742
01/01/2007 01:11:05 4747466
01/01/2007 01:12:05 8747482
01/01/2007 01:13:05 87572
01/01/2007 01:14:05 54765480
01/01/2007 01:15:05 43264326

Results from query:

TimeBlock When AvgOfThrouhput
11255916 01/01/2007 01:01:00 2147006.16666667
11255917 01/01/2007 01:03:05 109735652.866667
11255918 01/01/2007 01:08:05 16124529.4444444
11255919 01/01/2007 01:13:05 32705792.6666667

Thanks,

Mark.
 
 
 

Averaging values taken every 30 seconds into 5 minute interval

Post by Michael Gr » Sun, 27 Jan 2008 23:46:35


Using your data:

SELECT DATEADD("n",(DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5,0) AS [Time
Block],
Avg([DataRates].Throughput) AS [Avg Rate]
FROM DataRates
GROUP BY (DATEDIFF("n",0,[DataRates].Time_Stamp)\5)*5;



Time Block Avg Rate
1/1/2007 1:00:00 AM 69410024.3333333
1/1/2007 1:05:00 AM 67075760.6428571
1/1/2007 1:10:00 AM 14484951
1/1/2007 1:15:00 AM 43264326

 
 
 

Averaging values taken every 30 seconds into 5 minute interval

Post by TWFyayBSb3 » Mon, 28 Jan 2008 01:23:00

Michael,

Thank you, it works great.

Thanks to everyone for their assistance.

Regards,

Mark.
 
 
 

Averaging values taken every 30 seconds into 5 minute interval

Post by VG9kZ » Thu, 18 Sep 2008 00:40:01

Michael,

How would you do a similar query but for every 5 seconds given the data
was taken every second?