SohamC SohamC - 3 months ago 12
SQL Question

SQL GroupBy DateTime over X minutes

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

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;