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

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

Re: tricky group by questions

From: Job Miller <jobmiller_at_yahoo.com>
Date: Tue, 27 Nov 2007 08:44:11 -0800 (PST)
Message-ID: <353361.81260.qm@web53903.mail.re2.yahoo.com>


Are you looking for partition outer join functionality?

http://www.oracle-developer.net/display.php?id=312

If you don't have 10g, the above article talks about how to simulate partition outer join as well with inline views.

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



Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
--

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

Original text of this message

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