count distinct hour from datetime

Question

I am working with sql database, and I have problem. It is about public transportation, and I have table 'Traffic accidents' and column 'Time when accident happened' (datetime). I need to count how many accidents happened in specific HOUR, to realize what is the most risking part of day. I know how to separate hour from datetime, but the problem is how to count and order.. It should be something like this:

Time of traffic accidents

 2011-03-05 07:40:00.000,        
 2011-03-07 01:15:00.000,      
 2011-04-07 19:00:00.000,      
 2011-07-23 11:00:00.000,        
 2011-06-22 07:09:00.000,
 2011-03-08 07:14:00.000,
 2011-02-02 01:26:00.000  

Order accidents by hour

7h- 3 accidents,
1h- 2 accidents,
6h- 1 accident,
etc…

My English is not perfect, but I hope that I was clear enough :P Thank you, Urosh

Answer 1

try something like this:

WITH T as
(
 SELECT '2011-03-05 07:40:00.000' Col1 UNION ALL        
 SELECT '2011-03-07 01:15:00.000' Col1 UNION ALL  
 SELECT '2011-04-07 19:00:00.000' Col1 UNION ALL        
 SELECT '2011-07-23 11:00:00.000' Col1 UNION ALL          
 SELECT '2011-06-22 07:09:00.000' Col1 UNION ALL  
 SELECT '2011-03-08 07:14:00.000' Col1 UNION ALL  
 SELECT '2011-02-02 01:26:00.000' 
) 

Suppose T is your Table and Col1 is your datecolumn.

SELECT DATEPART(hour,Col1) as Hour,COUNT(DATEPART(hour,Col1)) as Accidents FROM T
GROUP BY DATEPART(hour,Col1)
ORDER BY DATEPART(hour,Col1)

Result:

Hour Accidents
1    2
7    3
11   1
19   1

Best Regards