Ryan,
I'd rather run something like
select ref.ref_time, coalesce(x.cnt, 0)
from (select suitably_formatted(your_min_date + rownum / 24) ref_time
from dual
connect by level <= ceil((your_max_date - your_min_date) * 24)) ref,
left outer join (your_query_here - rename count(*) as cnt) x
on x.time = ref.ref_time
HTH
SF
On Die Nov 27 16:31 , ryan_gaffuri@xxxxxxxxxxx sent:
I am writing a query that is grouping by 1 hour blocks over a period of time as follows
I am pretty sure the answer involves using "where not exists", but I can't get the dates I want to return.
select to_char(mydate, 'yyyymmdd hh24') , count(*)
from mytab
where mydate < sysdate
and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')
and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
group by to_char(mydate, 'yyyymmdd hh24')
order by to_char(mydate, 'yyyymmdd hh24') desc
Now I have one hour periods that do not have any rows. A standard group by just ignores those periods. I want periods with no data to return and have a count(*) = 0
so I would have
2007111101 20
2007111102 0
2007111103 10
now it returns as
2007111101 20
2007111103 10
--
//www.freelists.org/webpage/oracle-l
