By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Returning 0, possibly OUTER JOIN???

P: 1
Hi guys,

I have the following query:

Expand|Select|Wrap|Line Numbers
  1. (SELECT extract(hour from request_time) || ':00-' || ((extract(hour from request_time))+1) || ':00' AS time, count(*) as hits
  2. FROM log_request 
  3. GROUP BY extract(hour from request_time)
  4. ORDER BY extract(hour from request_time))
  5. UNION ALL
  6. (SELECT 'Total', count(*)
  7. 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.
Feb 19 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.