SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How do I get a average count per hour,per day.

390538Aug 26 2003 — edited Aug 28 2003

Can some one help me solve this. My query below generates the follow results:

TO_DATE(S DECOD
--------- -----
07-NOV-00 8am
07-NOV-00 9am
07-NOV-00 9am
07-NOV-00 10am
07-NOV-00 10am
07-NOV-00 10am
07-NOV-00 10am
07-NOV-00 10am

08-NOV-00 8am
08-NOV-00 8am
08-NOV-00 9am
08-NOV-00 9am
08-NOV-00 10am
08-NOV-00 10am
08-NOV-00 10am

09-NOV-00 8am
09-NOV-00 8am
09-NOV-00 9am
09-NOV-00 9am
09-NOV-00 10am
09-NOV-00 10am
09-NOV-00 10am
09-NOV-00 10am
09-NOV-00 10am

**** My code *****
Select
TO_DATE(SUBSTR(TIMESTAMP,1,8),'YYYYMMDD'),
DECODE(SUBSTR(TIMESTAMP,9,2),
00,'12am',
01,'1am',
02,'2am',
03,'3am',
04,'4am',
05,'5am',
06,'6am',
07,'7am',
08,'8am',
09,'9am',
10,'10am',
11,'11am',
12,'12pm',
13,'1pm',
14,'2pm',
15,'3pm',
16,'4pm',
17,'5pm',
18,'6pm',
19,'7pm',
20,'8pm',
21,'9pm',
22,'10pm',
23,'11pm', 'error')
from Pings
Where rownum < 1001
GROUP BY TIMESTAMP
**** End My Code ****

Thanks in advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 25 2003

Added on Aug 26 2003

4 comments

2,013 views