Create a SELECT statement with a GROUP BY

Question

How can I create a select statement with a group by return values for all hours even when there is data in the database for some hours?

I have the following query:

SELECT 
    DAY(OccurredAt) AS [Day], 
    DATEPART(HOUR,OccurredAt) AS [Hour], 
    COUNT(ID) AS [Errors]
FROM 
    Database..Error WITH (NOLOCK)
WHERE 
    YEAR(OccurredAt) = 2012 
    AND MONTH(OccurredAt) = 5
GROUP BY 
    DAY(OccurredAt), DATEPART(HOUR,OccurredAt)
ORDER BY 
    DAY(OccurredAt), DATEPART(HOUR,OccurredAt)

It returns data like this:

Day Hour    Errors
1   1   2
1   4   2
1   6   1
1   7   1
1   9   3
1   10  1
1   11  1
1   14  19
1   15  7
1   16  234
1   17  54
1   18  17
1   19  109
1   20  27
1   22  2
2   6   2
2   7   1
2   8   2
2   9   1
2   10  44
2   11  2
2   15  1
2   16  3
2   18  2
2   19  41
2   20  108
2   21  106
2   22  36
2   23  2

I would like it to return data like this:

Day Hour    Errors
1   0   0
1   1   2
1   2   0
1   3   0   
1   4   2
1   5   0
1   6   1
1   7   1
1   8   0
1   9   3
1   10  1
1   11  1
1   12  0
1   13  0
1   14  19
1   15  7
1   16  234
1   17  54
1   18  17
1   19  109
1   20  27
1   21  0
1   22  2
1   23  0

So basically I need the hours where there are zero errors to show in the query results as well. These will need to show for the entire date range, in this case all of May 2012.

Tried a few things but not had any luck so far.

Answer 1

Instead of using temporary tables or CTEs, use permanent tables. It is extremely useful in almost all databases to have a numbers (or integers) table and a calendar table. Then queries like yours become easy, because it's simple to do an outer join on those tables to fill in missing numbers or dates that are not present in the 'real' data. That's in addition to their many other uses, of course.

The alternative is scattering duplicate CTEs and/or unmaintainable hard-coded functions through your code.

Answer 2

This is the query I use for selecting stuff on a per day basis:

WITH Dates AS (
SELECT
 [Date] = CONVERT(DATETIME,'01/01/2012')
UNION ALL SELECT
 [Date] = DATEADD(DAY, 1, [Date])
FROM
 Dates
WHERE
 Date < '12/31/2012'
) 
SELECT [Date]
FROM Dates
OPTION (MAXRECURSION 400)

You can extend it the way you like with joins on the date to the table you need.

Not the 100% answer perhaps but this should help you get on your way.

EDIT: Recursive CTEs can perform poorly. so use wisely