Home » SQL & PL/SQL » SQL & PL/SQL » Count per Hour (Toad)
Count per Hour [message #378677] Wed, 31 December 2008 05:09 Go to next message
suryabay
Messages: 2
Registered: December 2008
Location: Indonesia
Junior Member
Please..
I need to count transaction "per hour" from 00:00 until 23.00 occurs last day....

example Result:

Action_Date COUNT(*)
-------------------- ----------
31-12-08 00:00 5
31-12-08 01:00 12
31-12-08 02:00 8
31-12-08 03:00 20
.
.
31-12-08 23:00 15

Any ideas on how to do this simply?

thx...

Re: Count per Hour [message #378679 is a reply to message #378677] Wed, 31 December 2008 05:19 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; now we know the result.

What is the input?
Re: Count per Hour [message #378687 is a reply to message #378679] Wed, 31 December 2008 05:52 Go to previous messageGo to next message
suryabay
Messages: 2
Registered: December 2008
Location: Indonesia
Junior Member
Sorry...
I mean, I request what is the input if I want the result like it?

Thanks
Re: Count per Hour [message #378693 is a reply to message #378687] Wed, 31 December 2008 07:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The best input you could use would be exactly the output you want.

Then you can just do an

select * from input;


to get the output you want.

Confused
Re: Count per Hour [message #378699 is a reply to message #378677] Wed, 31 December 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sysdate, trunc(sysdate,'hh24') from dual;
SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
31/12/2008 15:47:29 31/12/2008 15:00:00

1 row selected.

Regards
Michel
Re: Count per Hour [message #378719 is a reply to message #378687] Wed, 31 December 2008 21:59 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi,
Try following example data and select query. Hope it will help you.


create table test (date_col date);

insert into test (date_col)
values ( to_date('31-12-08 01:25:12', 'dd-MM-yy hh24:MI:SS') );

insert into test (date_col)
values ( to_date('31-12-08 01:30:32', 'dd-MM-yy hh24:MI:SS') );

insert into test (date_col)
values ( to_date('31-12-08 02:31:31', 'dd-MM-yy hh24:MI:SS') );

insert into test (date_col)
values ( to_date('31-12-08 02:30:29', 'dd-MM-yy hh24:MI:SS') );

insert into test (date_col)
values ( to_date('31-12-08 02:49:25', 'dd-MM-yy hh24:MI:SS') );
.....
.....
.....


select to_char(trunc(date_col, 'hh24'), 'dd-MM-yy hh24:MI:SS'), count(*)
from test
group by to_char(trunc(date_col, 'hh24'), 'dd-MM-yy hh24:MI:SS')
order by 1;

Regards

Danish
Re: Count per Hour [message #378734 is a reply to message #378719] Thu, 01 January 2009 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Your post does not add anything to mine but it prevents OP from searchind, finding and learning.

In addition, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Count per Hour [message #378789 is a reply to message #378734] Thu, 01 January 2009 21:16 Go to previous message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Sorry Michel, Yes you are right. But I just put this example because this post is from a junior member having only 2 posts.

But again, you are absolutely right and again sorry.

Regards

Danish
Previous Topic: Tables with Composite Indexes
Next Topic: run sql queries as a script
Goto Forum:
  


Current Time: Wed Dec 07 12:42:18 CST 2016

Total time taken to generate the page: 0.24689 seconds