sql-serverssms-2014

Filter a column based on value and get count

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)

Solution

  • 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)