RE: tricky group by questions

Would something like this be feasible?

select select to_char(mydate, 'yyyymmdd hh24') , count (*) over (partition by to_char(sysdate, 'YYYYMMDD HH24')) from mytab
where mydate > to_date('20071125 0000', 'yyyymmdd hh24mi')

and mydatedate < to_date('20071125 2400', 'yyyymmdd hh24mi')
group by to_char(mydate, 'yyyymmdd hh24')
order by to_char(mydate, 'yyyymmdd hh24') desc

-----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





______________________________________________________________________

This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 


______________________________________________________________________



NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this electronic mail transmission may be confidential. This electronic mail transmission is intended for the addressee(s) only. Any unauthorized disclosure, reproduction, or distribution of, and/or any unauthorized action taken in reliance on the information in this electronic mail is prohibited. If you believe that you have received this electronic mail transmission in error, please notify the sender by reply transmission, or contact helpdesk_at_multiplan.com, and delete the message without copying or disclosing it. 



______________________________________________________________________

This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 


______________________________________________________________________

--
http://www.freelists.org/webpage/oracle-l