Skip to Main Content

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!

Show data in percentage groups based on elapsed time

Padmocho-OracleMar 29 2016 — edited Mar 31 2016

HI All,

I have a table where each event is stamped with a timestamp:

ID   Time

1   10:00:00

2   10:00:05 ( < 5 secs) -> "ok"

3   10:00:15 (5-15 secs) -> "short"

4   10:00:40 (15-30 sec) -> "medium"

5   10:01:15 ( > 30 secs) -> "long"

What I need is to group all readings into 4 categories:

1: all readings that were done within 5 sec to be marked as "ok"

2: all readings that were done between 5-15 secs to be marked as "short"

3: etc.

So, in the end - I would like to have an aggregation/analytical table showing percentage of each category in the whole day, e.g.

OK   Short   Medium   Long

60%  20%      15%         5%

Basically, I've been trying to come up with a good way to calculate the difference between time (in secs) by ordering all rows by time and then adding a rownum column to identify it and then I stuck how to move forward

Any help will be appreciated!

Regards,

Pawel

This post has been answered by Etbin on Mar 30 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2016
Added on Mar 29 2016
35 comments
4,934 views