June 26, 2018 at 8:00 am
I have a large query. I would like to count the number of instances in an hour.
Is it possible to use row partition to achieve this?
My table is as follows:
VisitTimes
00:20:00
01:25:00
01:30:00
01:45:00
02:01:00
02:02:00
02:05:00
02:06:00
03:10:00
03:12:00
03:13:00
So the results would be
midnight would be 1
1am would be 3
2am would be 4
3am would be 3
June 26, 2018 at 8:19 am
dbman - Tuesday, June 26, 2018 8:00 AMI have a large query. I would like to count the number of instances in an hour.
Is it possible to use row partition to achieve this?
My table is as follows:
VisitTimes
00:20:00
01:25:00
01:30:00
01:45:00
02:01:00
02:02:00
02:05:00
02:06:00
03:10:00
03:12:00
03:13:00So the results would be
midnight would be 1
1am would be 3
2am would be 4
3am would be 3
Just need to use GROUP BY on hour, no need for partition. If you include DDL & INSERT statements, someone will give you a working solution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 26, 2018 at 8:54 am
As Phil mentioned, you can use Datepart and Group By to achieve this.
DECLARE @t table (TimeValues time)
INSERT INTO @t VALUES
('00:20:00'),
('01:25:00'),
('01:30:00'),
('01:45:00'),
('02:01:00'),
('02:02:00'),
('02:05:00'),
('02:06:00'),
('03:10:00'),
('03:12:00'),
('03:13:00')
SELECT DATEPART(hh,Timevalues) AS HourOfDay,COUNT(DATEPART(hh,Timevalues)) AS Total
FROM @t
GROUP BY DATEPART(hh,Timevalues)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply