OraFAQ Forum: SQL & PL/SQL » Count per Hour

Home » SQL & PL/SQL » SQL & PL/SQL » Count per Hour (Toad)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count per Hour [message #378677]

Wed, 31 December 2008 05:09

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

Report message to a moderator

Re: Count per Hour [message #378679 is a reply to message #378677]

Wed, 31 December 2008 05:19

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK; now we know the result.

What is the input?

Report message to a moderator

Re: Count per Hour [message #378687 is a reply to message #378679]

Wed, 31 December 2008 05:52

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

Report message to a moderator

Re: Count per Hour [message #378693 is a reply to message #378687]

Wed, 31 December 2008 07:10

ThomasG
Messages: 3212
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

Report message to a moderator

Re: Count per Hour [message #378699 is a reply to message #378677]

Wed, 31 December 2008 08:47

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Count per Hour [message #378719 is a reply to message #378687]

Wed, 31 December 2008 21:59

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

Report message to a moderator

Re: Count per Hour [message #378734 is a reply to message #378719]

Thu, 01 January 2009 00:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Count per Hour [message #378789 is a reply to message #378734]

Thu, 01 January 2009 21:16

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 14:08:36 CST 2025