RE: tricky group by questions
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <ryan_gaffuri@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Nov 2007 12:06:40 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
