RE: tricky group by questions
- From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <ryan_gaffuri@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Nov 2007 10:58:25 -0600
Ahhh...There is no row in there for that interval, therefore it wouldn't
return a 0.....duhhhhhh. My fault on my earlier replies...
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 Mercadante, Thomas F
(LABOR)
Sent: Tuesday, November 27, 2007 10:45 AM
To: ryan_gaffuri@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: tricky group by questions
Ryan,
This is a pretty stupid way to do this, but it works. You are not
getting counts in your query because there are no rows to count. The
query below creates 24 rows (1 per hour) to force Oracle to give you a
row. Union that result set with your query, sum the results and
subtract 1 and you get your result.
Tom
select distinct sub.hour, sum(sub.count)-1
from (
select to_number(to_char(mydate,'hh')) hour, count(*) count
from mytab
group by to_char(mydate,'hh')
union
select rownum,count(*)
from all_objects
where rownum < 25
group by rownum
) sub
group by sub.hour
-----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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
