CODEWITHSUNDEEP
sqldatabasesql-server-2008group-by
Peter H
Peter H

Reputation: 901

SQL Server 2008 Retrieve Average values by time

I have the following table shown below.

Sample Dataset

What I am after is the SQL to retrieve the 5 minute average on the value field grouped by time. My desired output after running the query should look like this:
Required Output

Alternatively, I'm hoping to be able to easily modify the SQL to obtain 10 minute averages, daily averages etc.

The SQL used to create the sample_table below shown above is below.

CREATE TABLE dbo.sample_table
(
id int NULL,
time_value datetime NULL,
value real NULL
)  ON [PRIMARY];

insert INTO sample_table VALUES (1,'2013-12-23 08:00:00.000',86);
insert INTO sample_table VALUES (2,'2013-12-23 08:02:00.000',49);
insert INTO sample_table VALUES (3,'2013-12-23 08:03:00.000',27);
insert INTO sample_table VALUES (4,'2013-12-23 08:05:00.000',81);
insert INTO sample_table VALUES (5,'2013-12-23 08:07:00.000',48);
insert INTO sample_table VALUES (6,'2013-12-23 08:08:00.000',52);
insert INTO sample_table VALUES (7,'2013-12-23 08:09:00.000',57);
insert INTO sample_table VALUES (8,'2013-12-23 08:10:00.000',62);
insert INTO sample_table VALUES (9,'2013-12-23 08:11:00.000',75);
insert INTO sample_table VALUES (10,'2013-12-23 08:12:00.000',90);

Any assistance is appreciated.

Upvotes: 1

Views: 286

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You need to round the timevalue to the nearest five minutes. This is a bit ugly in SQL Server, but doable:

select dateadd(minute, 5*(datediff(minute, 0, time_value)/5), 0) as MyTimeValue,
       avg(cast(value as float))
from t
group by dateadd(minute, 5*(datediff(minute, 0, time_value)/5), 0);

Note: value looks like an integer. SQL Server calculates the average of an integer as an integer. So, the average of 1, 1, and 2 is 1 rather than 1.333.

Upvotes: 2

Related Questions