# Grouping count by interval of 15 minutes

I was trying to write a query where I can group the data by 15 minutes interval.

Example:

I want to count the orders by interval. So Output should be:

How can this be written?

• And what if a particular 15-minute interval doesn't have data, would it be fine to skip it in the output? Or would you still like it to be included in the output with the count of 0? Commented Jul 14, 2017 at 8:49

If you can deal with not counting zeros, you can avoid a calendar table

``````SELECT
DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101'),
count(*)
FROM
Transactions
GROUP BY
DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101')
``````

gives

``````2017-07-10 11:30:00.000 2
2017-07-10 11:45:00.000 1
2017-07-10 12:00:00.000 5
2017-07-10 12:15:00.000 1
``````

If you will usually use it, I'd suggest to create a calendar table. In this case this is the minimum expression of this calendar table, you can find a lot of samples googling a bit.

``````CREATE TABLE MyCalendar (QtHour1 datetime, QtHour2 datetime);

DECLARE @StartDate datetime = '20170101 00:00:00';
DECLARE @EndDate datetime = '20180101 00:00:00';

DECLARE @dt datetime;
SET @dt = @StartDate;

WHILE @dt < @EndDate
BEGIN
INSERT INTO MyCalendar VALUES(@dt, DATEADD(MINUTE, 15, @dt));
SET @dt = DATEADD(MINUTE, 15, @dt)
END;
GO
``````
```35040 rows affected
```
``````CREATE TABLE Transactions(TransactionDate datetime, OrderCount int);
INSERT INTO Transactions VALUES
('20170710 11:37:49', 1),
('20170710 11:41:29', 1),
('20170710 11:58:40', 1),
('20170710 12:04:28', 1),
('20170710 12:05:08', 1),
('20170710 12:10:49', 1),
('20170710 12:11:54', 1),
('20170710 12:12:37', 1),
('20170710 12:15:00', 1);
GO
``````
```9 rows affected
```

Then you can GROUP BY (no matter which field of your calendar table), and get the desired result.

``````SELECT   QtHour1,
SUM(OrderCount) as Orders
FROM     MyCalendar
JOIN     Transactions
ON       TransactionDate >= QtHour1
AND      TransactionDate < QtHour2
GROUP BY QtHour1
ORDER BY QtHour1;
GO
``````
```QtHour1             | Orders
:------------------ | -----:
10/07/2017 11:30:00 |      2
10/07/2017 11:45:00 |      1
10/07/2017 12:00:00 |      5
10/07/2017 12:15:00 |      1
```

dbfiddle here