RE: possible to do a group by on a time interval? (or some other way to do this)

select sum(mymetric) as bytes
from metrictable
where mytimestamp >= ( select max(mytimestamp) - 1/96 from metrictable);

Igor

-----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: Wednesday, October 10, 2007 6:12 PM To: oracle-l_at_freelists.org
Subject: possible to do a group by on a time interval? (or some other way to do this)

seselect max(mytimestamp) as time, sum(mymetric) as bytes from metrictable where mymetric > ( select max(mymetric) from metrictable) - (1/96)

"mytimestamp" is a timestamp data type.

I want to get a sum for every 15 minute interval in the table. I will add a where clause to limit how far back I want to go. The where clause I have there now is just an example.

tried group by, group by rollup, analytic with a window.

do i need the model clause? I have never used that. I really do think there is a way to do this in straight sql. Been working on it for a while....
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 11 2007 - 08:13:30 CDT