Is there a way to GROUP BY a time interval in this table?

Question

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)

Answer 1

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