I have the following query:
Expand|Select|Wrap|Line Numbers
- (SELECT extract(hour from request_time) || ':00-' || ((extract(hour from request_time))+1) || ':00' AS time, count(*) as hits
- FROM log_request
- GROUP BY extract(hour from request_time)
- ORDER BY extract(hour from request_time))
- UNION ALL
- (SELECT 'Total', count(*)
- FROM log_request);
00:00-01:00 | 4
10:00-11:00 | 10
Total | 14
Now I want to modify this query so that even if a hourly time slot does not appear in the database to still return it with a value of 0, i.e.
00:00-01:00 | 4
01:00-02:00 | 0
Now it has suggested to me that this may be possible by creating another table called hours and in this table just have a single value for each time slot, i.e. 0 for 00:00-01:00, 1 for 01:00-02:00 and then doing an outer join but so far I have been unsuccessful.
Could anyone suggest a possible solution?
Thanks in advance, Scott.