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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of ryan_gaffuri@xxxxxxxxxxx
Sent: Tuesday, November 27, 2007 10:31 AM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: