Returning 0, possibly OUTER JOIN???

[scotthendo]

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.