I have a table like this one:
DateTime A
10:00:01 2
10:00:07 4
10:00:10 2
10:00:17 1
10:00:18 3
Is this possible to create a query that returns me the average value of A each 10 seconds? In this case the result would be:
3 (4+2)/2
2 (2+1+3)/3
Thanks in advance!
EDIT: If you really think that this can not be done just say NO WAY! :) It's an acceptable answer, I really don't know if this can be done.
EDIT2: I'm using SQL Server 2008. I would like to have different groupings but fixed. For example, ranges each 10 sec, 1 minute, 5 minutes, 30 minutes, 1 hour and 1 day (just an example but something like that)
In SQL Server, you can use DATEPART and then group by hour, minute and second integer-division 10.
CREATE TABLE #times
(
thetime time,
A int
)
INSERT #times
VALUES ('10:00:01', 2)
INSERT #times
VALUES ('10:00:07', 4)
INSERT #times
VALUES ('10:00:10', 2)
INSERT #times
VALUES ('10:00:17', 1)
INSERT #times
VALUES ('10:00:18', 3)
SELECT avg(A) -- <-- here you might deal with precision issues if you need non-integer results. eg: (avg(a * 1.0)
FROM #times
GROUP BY datepart(hour, thetime), DATEPART(minute, thetime), DATEPART(SECOND, thetime) / 10
DROP TABLE #times
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With