Home » SQL & PL/SQL » SQL & PL/SQL » Query to retrive the number transactions done in every 1 hour for last one month (Oracle 10g)
Query to retrive the number transactions done in every 1 hour for last one month [message #356385] Thu, 30 October 2008 10:21 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

Could anyone help in writing a query to retrieve the number of transactions done in every 1 hour for last month.

Case:
I/P

Cases Timestamp1
case1 01-01-2008 00:00:01
case2 01-01-2008 00:01:01
case3 01-01-2008 01:00:01
case1 01-01-2008 01:02:01
case4 01-01-2008 01:10:01
case5 02-01-2008 02:00:01
case6 02-01-2008 02:10:01
case7 02-01-2008 23:00:01
.............................

.............................
case.. 31-01-2008 24:00:00


O/P
from time to_time cases
01-01-2008 00:00:00 01-01-2008 01:00:00 2
01-01-2008 01:00:01 01-01-2008 02:00:00 3

.........................
.........................

etc

Any help really appreciated
Re: Query to retrive the number transactions done in every 1 hour for last one month [message #356392 is a reply to message #356385] Thu, 30 October 2008 10:46 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@vikram1780,

Please specify what you have tried so far and where you are facing problems in your queries.

Also, Please Read OraFAQ Forum Guide on "How to Format Your Post"

TO_CHAR Function in a GROUP BY Clause will be a good hint I guess...

Regards,
Jo

[Updated on: Thu, 30 October 2008 10:48]

Report message to a moderator

Re: Query to retrive the number transactions done in every 1 hour for last one month [message #356393 is a reply to message #356385] Thu, 30 October 2008 10:52 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
AS I am a newbie.I don't know how to write a group by clause with to)char function for hours.

Any help really appreciated.
Re: Query to retrive the number transactions done in every 1 hour for last one month [message #356400 is a reply to message #356393] Thu, 30 October 2008 11:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Post some test data in the form of insert statements. Also post what you have tried so far.

O.k try this and try to build your query based on this.
with t
as
(select sysdate + level / (24 * 60) dt from dual connect by level <= 100)
select trunc(dt,'hh24'), count(*) from t
group by trunc(dt,'hh24')

Regards

Raj

[Updated on: Thu, 30 October 2008 11:16]

Report message to a moderator

Re: Query to retrive the number transactions done in every 1 hour for last one month [message #356414 is a reply to message #356393] Thu, 30 October 2008 12:03 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@vikram1780,

Please go through the links I had posted. They contain simple examples for you to try out. If you face any problems come back. Will be more than happy to guide you...

Regards,
Jo
Previous Topic: Is this possible?
Next Topic: SQL Command to list tables
Goto Forum:
  


Current Time: Thu Dec 08 06:04:16 CST 2016

Total time taken to generate the page: 0.11357 seconds