CODEWITHSUNDEEP
sqlsql-server-2008
user2979612
user2979612

Reputation: 177

Count distinct values in an SQL table for each hour in a day

I have a table that looks like this:

ID    TimeStamp    CarNumber
1    2018\12\03 14:05:32    433
2    2018\12\03 14:13:52    420
3    2018\12\03 14:55:14    433
4    2018\12\03 15:12:03    420
5    2018\12\03 16:15:55    570

I have to get the number of distinct car numbers for each hour of the day, so what I do now is run a command for each hour separately:

SELECT DISTINCT CarNumber
FROM Cars
WHERE TimeStamp BETWEEN '2018\12\03 14:00:00' AND '2018\12\03 15:00:00'

Is there a way to build a table that will do that for each hour of the day automatically? Expected output:

Hour    NumberOfCars    CarNumbers
0       0               0
...
14      2               433, 420
15      1               420
16      1               570
...

Upvotes: 1

Views: 1280

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below. Extract hours from datetime column and use in group by 

 select convert(date,TimeStamp),
 DATEPART(HOUR, TimeStamp) ,
 count(distinct CarNumber) numofcars
 from Cars 
 group by convert(date,TimeStamp),DATEPART(HOUR, TimeStamp)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can aggregate by date and hour:

select cast(timestamp as date) as thedate,
       datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by cast(timestamp as date), datepart(hour, timestamp)
order by thedate, thehour;

You can add a where clause if you want the results for only one date. If you want the results aggregated over multiple days, remove thedate from the logic:

select datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by datepart(hour, timestamp)
order by thehour;

Upvotes: 2

Related Questions