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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Tuesday, November 27, 2007 10: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 - 10:44:47 CST