Get counts by hour even when count is 0
450790Jan 3 2012 — edited Jan 3 2012Hi,
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.