Home » SQL & PL/SQL » SQL & PL/SQL » Event Count (Oracle 9i R2)
Event Count [message #339458] Thu, 07 August 2008 09:43 Go to next message
uttamkumar
Messages: 2
Registered: July 2008
Location: Chennai
Junior Member

Hi Guru's

I having set of data like

Event Time Event Type Event ID
08-01-2008 01:45:20 AAA 1
08-01-2008 01:46:42 AAA 2
08-01-2008 01:47:20 BBB 3
08-01-2008 01:47:20 AAA 3
08-01-2008 01:49:20 AAA 4
08-01-2008 01:50:20 CCC 5
08-01-2008 01:52:00 AAA 6
08-01-2008 01:54:48 AAA 7
08-01-2008 01:56:20 BBB 8
08-01-2008 01:57:20 CCC 9

My requirement is write such a query to count total no event for interval of 5 Min each as per the event type.

Could any one help me to write such a query...

Regards,
Uttam

Re: Event Count [message #339459 is a reply to message #339458] Thu, 07 August 2008 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW Posting Guidelines as stated in URL above.
Re: Event Count [message #339465 is a reply to message #339458] Thu, 07 August 2008 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result
Make sure that lines of code do not exceed 80 characters when you format.

Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Event Count [message #339748 is a reply to message #339458] Fri, 08 August 2008 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should give you a good pointer as to how to group the dates into 5 minute sections:
with src as (select sysdate + (level/(24*60)) dte from dual connect by level <= 300)
select to_char(dte,'dd.mm.yyyy hh24:mi:ss') actual_date
      ,to_char(trunc(dte) + (to_number(to_char(dte,'SSSSS'))-mod(to_number(to_char(dte,'SSSSS')),(5*60)))/(24*60*60),'dd.mm.yyyy hh24:mi:ss') five_min_date from src;
Re: Event Count [message #339773 is a reply to message #339748] Fri, 08 August 2008 07:44 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Fri, 08 August 2008 07:27
This should give you a good pointer as to how to group the dates into 5 minute sections


[ Evil grin ]
I have a feeling the OP head just exploded, as it took me a while to figure it out.
[ /Evil grin ]
Previous Topic: Trigger
Next Topic: CAN YOU HELP ME GET THE LOGIC
Goto Forum:
  


Current Time: Thu Dec 08 14:24:06 CST 2016

Total time taken to generate the page: 0.15129 seconds