Ryan wrote:
>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
Unless you are joining to a table / view that defines the periods, you can't
have the missing periods in the result of your group by. If they're not in the
ungrouped data, how can they be in the aggregate?
Of course it is easy to define a view / query to construct those periods you
need on the fly - something like (no test system so apologies for typos):
with period_list as (
select to_date('20071125 1500', 'yyyymmdd hh24mi') --- YOUR START TIME
HERE (as a date)
+ (rownum/24) hh24
from all_objects
where rownum < 2 --- THE NUMBER OF
PERIODS YOU WANT
)
select to_char(period_list.hh24, 'yyyymmdd hh24') , count(*)
from period_list
left join mytab on trunc(mydate,'HH24') = period_list.hh24 --- JOIN REMOVES
NEED FOR WHERE
group by period_list.hh24
order by period_list.hh24 desc
YMMV - other ways of expressing the same concept may scale better, depending on
the size of mytab and the number of periods involved...
HTH
Regards Nigel