Filter a column based on value and get count

Question

I have a column Startime datetime2(7) and the data format is 2019-05-15 22:33:55.0000000.

I'm trying to filter column and get the count by hour

SELECT * 
FROM vehicles 
WHERE vendor = 'ford' 
  AND starttime BETWEEN '05/01/2019 00:00:00' AND '05/15/2019 23:59:59'

This query returns all results between the dates; can anyone suggest how to query and get count by hourly basis?

For example between may 1 2019 and may 15 2019 the count and the result like

 - 0 hour countX
 - 1 hour countY
 - 2 hour countZ
 - ...
 - ...
 - 23 hour countXY

Count for all between days (result expected is 24 rows)

Your hour 0 belongs to day 1 to 15 for all day? Or you need Hours per day? Also please mention the database name. — mkRabbani, May 16 '19 at 18:22
overall count for 1 to 15 and result should be 24 rows @paolo — Mr_gemini, May 16 '19 at 18:31

score 1 · Accepted Answer · answered May 16 '19 at 18:34

Check this. This will give your desired output with only Hour wise breakdown-

SELECT 
CAST(DATEPART(HH,starttime) AS VARCHAR) +' Hour Count ' + CAST(COUNT(*) AS VARCHAR)
FROM vehicles 
WHERE vendor='ford' 
AND starttime BETWEEN '05/01/2019 00:00:00' AND '05/15/2019 23:59:59'
GROUP BY  DATEPART(HH,starttime)

Filter a column based on value and get count

1 Answers1