Re: tricky group by questions
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: Chris.Taylor@xxxxxxxxxxxxxxx, ryan_gaffuri@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 27 Nov 2007 08:44:11 -0800 (PST)
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.
----- Original Message ----
From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
To: ryan_gaffuri@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, November 27, 2007 10:47:08 AM
Subject: 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@xxxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
ryan_gaffuri@xxxxxxxxxxx
Sent: Tuesday, November 27, 2007 9: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
--
//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/
Other related posts:
