Hi guys,
I have the following query:
- (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);
Which returns an hourly time slot and the amount of times that that hourly slot has appeared, i.e.
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.
