Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get counts by hour even when count is 0

450790Jan 3 2012 — edited Jan 3 2012
Hi,

I'm trying to write a query that will give me counts per hour on any given day even if the count is 0 for any particular hour

I'm trying to get results like below.

Buy Date Count
-------------------------------------
1/2/2011 8AM 103
1/2/2011 9AM 43
1/2/2011 10AM 0
1/2/2011 11AM 0
.
.
.
1/2/2011 10PM 5
1/2/2011 11PM 0

I have this query, but this gives only the rows where count is not zero.

select trunc(buyDate, 'hh') "Buy Date", count(1)
from cust_trxns
where buyDate >= '02-JAN-2011'
and buyDate >= '02-JAN-2011'

Thanks in advance for all your help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 31 2012
Added on Jan 3 2012
2 comments
2,726 views