Re: tricky group by questions

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Tue, 27 Nov 2007 09:28:52 -0800 (PST)
Message-ID: <326777.54767.qm@web58805.mail.re1.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 27 2007 - 11:28:52 CST