Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: tricky group by questions
Unless I'm mistaken, a group by will not ignore those rows especially
when using a count(*).
Either something else is disqualifying those rows from returning, or you might have a corrupt index if it is doing an index scan.
Also, your results don't match your query. Looks like you're looking for 11/11/2007 dates but you're limiting it to dates > 11/25/2007. I assume this was just an oversight.
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com
-----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 9: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 - 09:47:08 CST
![]() |
![]() |