CODEWITHSUNDEEP
sqltimeframesql-server-2012intervals
red
red

Reputation: 119

SQL - Group results by time interval

With the given table structure (eventTime is in seconds) I want to group the results by a given time frame:

For example: 5 minutes

0 to 5:   1 
5 to 10:  2
10 to 15: 3
.....

How can this be done in SQL Server 2012?

Thanks!

Florian

CREATE TABLE [dbo].[evalHubSupply](
[evalHubSupplyId] [int] IDENTITY(1,1) NOT NULL,
[projectId] [int] NOT NULL,
[scenarioId] [int] NOT NULL,
[iterationId] [int] NOT NULL,
[evalExportId] [int] NOT NULL,
[eventType] [varchar](50) NOT NULL,
[eventTime] [int] NOT NULL,
[stopId] [varchar](50) NOT NULL,
[stopName] [varchar](50) NULL,
[vehicleId] [varchar](50) NOT NULL,
[transitLineId] [varchar](50) NULL,
[transitRouteId] [varchar](50) NULL,
[capacity] [int] NULL,
[arrivalTimeAtStop] [int] NULL,
[agentsOnBoard] [int] NULL)

Sample data (interval 1 hour):

https://dl.dropbox.com/u/481455/table_data.xlsx or https://dl.dropbox.com/u/481455/table_data_open.ods

The tab "Table data" contains sample data from evalHubSupply. The column "Time interval" calculates the interval (in this case per hour), to which the eventTime relates. The results tabs counts how many events are related to a specific interval.

Upvotes: 4

Views: 2588

Answers (1)

Joe G Joseph
Joe G Joseph

Reputation: 24046

this will give number of records in every 5 min group

select eventTime/5,count(*)
from   evalHubSupply
group by eventTime/5

Upvotes: 1

Related Questions