SQL GroupBy DateTime over X minutes

Question

I have a query like this

select d.Data,
    CONVERT(VARCHAR(20), d.Time, 100) as [Time], 
    avg(d.Count) as [CountVal]
from data d
group by d.data, CONVERT(VARCHAR(20), d.Time, 100)

where I am grouping data on a minute basis. I wish to group the data every X minute then how can I do that?

For example, x=15 and we have the following data,

      Time              Data        Count
-------------------     -----       -----
Aug  8 2016  9:00AM     data1        11
Aug  8 2016  9:05AM     data2        12
Aug  8 2016  9:10AM     data3        47
Aug  8 2016  9:13AM     data3        20
Aug  8 2016  9:14AM     data1        12
Aug  8 2016  9:25AM     data3        61
Aug  8 2016  9:30AM     data2        35
Aug  8 2016  9:35AM     data1        16
Aug  8 2016  9:40AM     data1        92
Aug  8 2016  9:41AM     data2        19

I want the resultant data to be as follows

      Time              Data        Count
-------------------     -----       -----
Aug  8 2016  9:00AM     data1        23
Aug  8 2016  9:00AM     data2        12
Aug  8 2016  9:00AM     data3        67
Aug  8 2016  9:15AM     data3        61
Aug  8 2016  9:30AM     data1        108
Aug  8 2016  9:30AM     data2        54

Any ideas how we can achieve this?

Thanks in advance!

Answer 1

Via substracting a modulus on the minutes, and truncating to the minute:

declare @Xmin int = 15;

select CONVERT(VARCHAR(20), [Time], 100) as [Time], Data, count(*) as [Count]
from (
    select 
    dateadd(mi,-datepart(mi,[Time])%@Xmin,dateadd(mi,datediff(mi,0,[Time]),0)) as [Time], Data
    from data
) d
group by Data, [Time]
order by 1, 2;

Answer 2

It seems to me surprisingly hard to do in SQL, but you need to calculate the time rounded down to the last 5 minute amount, then group by that amount. and to do that I had to go through a few steps. How about I make a standard function for you, to help keep your code cleaner?

this is for 5 minute segments, I have datepart(minute, d.Time)) / 5) * 5, you can change both the 5's to any other whole number or make it into an integer variable - it takes the whole day part of your date (can be done in higher SQL versions by casting to date if you wanted) and then to that date, it rounds the number of minutes into the day to the lower 5, then adds them on

 select d.Data,
       dateadd(minute, 
                 floor((datepart(hour,d.Time) * 60 
                              + datepart(minute, d.Time)) / 5) * 5,          
                 cast(convert(nvarchar(8), d.Time, 112) as datetime)
               ),
  avg(d.Count) as [CountVal]
from data d

GROUP BY dateadd(minute, 
                 floor((datepart(hour,d.Time) * 60 
                              + datepart(minute, d.Time)) / 5) * 5,          
                 cast(convert(nvarchar(8), d.Time, 112) as datetime)
                 )

here it is done by a function that you have to first create

    -- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION fn_DateTime_n_MinuteSector
(
    -- Add the parameters for the function here
    @PDATE datetime,
    @PNMINS int
)
RETURNS datetime
AS
BEGIN

    DECLARE @WDATE datetime;
    DECLARE @DAYMINS int;
    DECLARE @ROUNDMINS int;

    --calculate Whole Date and number of complete minutes elapsed since start of date
    SELECT @WDATE = cast(convert(nvarchar(8), @PDATE, 112) as datetime), 
           @DAYMINS = datepart(hour,@PDATE) * 60 + datepart(minute, @PDATE);

    --round down to n minutes in day
    SELECT @ROUNDMINS = FLOOR(@DAYMINS / @PNMINS) * @PNMINS;

    --create rounded date by adding minutes on to date part
    RETURN dateadd(minute , @ROUNDMINS, @WDATE) ;


END
GO

then to use your function in a dbo.fn_DateTime_n_MinuteSector(d.time, 15)