tricky group by questions

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 27 Nov 2007 17:45:09 +0100
Message-Id: <10456.1196181909@roughsea.com>

Ryan,

I'd rather run something like

select ref.ref_time, coalesce(x.cnt, 0)

from (select suitably_formatted(your_min_date + rownum / 24) ref_time

from dual

connect by level <= ceil((your_max_date - your_min_date) * 24)) ref,

left outer join (your_query_here - rename count(*) as cnt) x

on x.time = ref.ref_time

HTH

On Die Nov 27 16:31 , ryan_gaffuri@comcast.net sent:

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:45:09 CST