Re: tricky group by questions

  • From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
  • To: ryan_gaffuri@xxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 27 Nov 2007 09:28:52 -0800 (PST)

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

Other related posts: