I am trying to count the rows per hour from a table in SQL Server. The result I'm looking for is to have a count for each hour of my data (even though the entries span over different minutes of each hour), essentially aggregating the data.
This is a similar question to Count rows per hour in SQL Server with full date-time value as result
The answer to this question is almost what I'm looking for, except it does not include hours for which the count was 0. So essentially I'm trying to fill in these gaps with zeroes, so that I can have 24 points for each day.
Any help appreciated
Just make a table with 24 rows (1-24) or make a CTE / derived table (select 1 union all select 2...) and add the rest of the query as outer join to this, then you'll get the missing rows