DDan DDan - 1 month ago 16
SQL Question

Group [time] by 5 sec intervals

I have data containing seconds [time] value. I'd like count my data based on how many records/second.

This works like this:

SELECT [Time], count(*)

FROM [dbo].[Times]
GROUP BY
[Time] ORDER By [Time]


This gives me:

+----------+----+
| 00:00:00 | 4 |
+----------+----+
| 00:00:01 | 2 |
+----------+----+
| 00:00:02 | 1 |
+----------+----+
| 00:00:08 | 2 |
+----------+----+
...


I'd like to group and count them in 5 second intervals, so the result would look like:

+----------+----+
| 00:00:05 | 7 | <-- # of records between 0:00:00 and 0:00:05
+----------+----+
| 00:00:10 | 2 | <-- # of records between 0:00:05 and 0:00:10
+----------+----+
...


Any good way yo do this in MS SQL?

Answer

Here is one method:

select cast(dateadd(second, (datediff(second, 0, [time]) / 5) * 5, 0) as time(0)) as [time],
       count(*)
from [dbo].[Times] t
group by dateadd(second, (datediff(second, 0, [time]) / 5) * 5, 0)
order by min([time]);
Comments