Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> tricky group by questions

tricky group by questions

From: <ryan_gaffuri_at_comcast.net>
Date: Tue, 27 Nov 2007 15:31:01 +0000
Message-Id: <112720071531.8531.474C3835000BB9FB000021532200734364079D9A00000E09A1020E979D@comcast.net>


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
Received on Tue Nov 27 2007 - 09:31:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US