RE: tricky group by questions

You'll have to create a master list of all the hours you want and then do an outer join to the data that you are counting with an nvl around the count.

Here is an example, I am not connected to the database right now so I can't test it for syntax errors.    

Select to_char(allhours, 'yyyymmdd hh24'), nvl(count(b.mydate),0)

from (select to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24 allhours

      from mytab -- or any small table with enough rows to meet the number of hours required

      where to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24 > to_date('20071125 1500', 'yyyymmdd hh24mi')

      and to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24 < sysdate) a,

      mytab b

where b.mydate(+) between a.allhours and a.allhours+3559/86400

and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')

group by to_char(allhours, 'yyyymmdd hh24')

order by allhours desc    

Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Tuesday, November 27, 2007 10:31 AM To: oracle-l_at_freelists.org
Subject: tricky group by questions  

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 27 2007 - 11:06:40 CST