2

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

Thank you

Community
  • 1
  • 1
user2381303
  • 31
  • 5

1 Answers1

2

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

James Z
  • 12,209
  • 10
  • 24
  • 44
  • If you need missing days too, you'll probably need to add a calendar table for that. It can be done with recursive CTE too, if the amount of data is really small. – James Z Feb 08 '15 at 15:59
  • Thank you for your reply. Unfortunately my data spans over a few years and will continue to expand into the future. Also I would like to avoid using 'hardcoded' date/time tables as they are fixed - I would prefer to obtain the first and last date of the dataset and somehow generate the missing date/time entries dynamically. – user2381303 Feb 08 '15 at 16:42
  • You can generate the table for example for next 50 years and just fetch those dates you need, e.g. up till today. That's probably still better than trying to build missing dates dynamically. – James Z Feb 08 '15 at 17:00
  • Any idea how to automatically populate such a table though? – user2381303 Feb 08 '15 at 17:03