CODEWITHSUNDEEP
sqloracle-database
Nishant Gupta
Nishant Gupta

Reputation: 19

Grouping rows on the basis of range

How to group data on the specific range of time in oracle.

My data as below:

HHMISS  No_of_Sessions
010101  2
010102  3
010104  5
010107  2
010108  1
010110  6
010111  8
010113  1
010115  6

My requirement is to get number of sessions for every 5 secs.

HHMISS  No_of_Sessions
010100  10
010105  9
010110  15

Upvotes: 1

Views: 57

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Your question is confusing because the results you posted do not meet the requirements you describe.

Here is what I think you are looking for:

SELECT lpad(floor(to_number(hhmiss)/5)*5,6,'0') grp, sum(no_of_session)
from test_data
group by lpad(floor(to_number(hhmiss)/5)*5,6,'0')
order by lpad(floor(to_number(hhmiss)/5)*5,6,'0')

This will group each input row into intervals of 5 seconds and then give you the number of sessions in each group.

Full example, with test data:

with test_data ( hhmiss, no_of_session ) as
(
SELECT '010101',  2 FROM DUAL UNION ALL
SELECT '010102',  3 FROM DUAL UNION ALL
SELECT '010104',  5 FROM DUAL UNION ALL
SELECT '010107',  2 FROM DUAL UNION ALL
SELECT '010108',  1 FROM DUAL UNION ALL
SELECT '010110',  6 FROM DUAL UNION ALL
SELECT '010111',  8 FROM DUAL UNION ALL
SELECT '010113',  1 FROM DUAL UNION ALL
SELECT '010115',  6 FROM DUAL )
SELECT lpad(floor(to_number(hhmiss)/5)*5,6,'0') grp, sum(no_of_session)
from test_data
group by lpad(floor(to_number(hhmiss)/5)*5,6,'0')
order by lpad(floor(to_number(hhmiss)/5)*5,6,'0')

+--------+--------------------+
|  GRP   | SUM(NO_OF_SESSION) |
+--------+--------------------+
| 010100 |                 10 |
| 010105 |                  3 |
| 010110 |                 15 |
| 010115 |                  6 |
+--------+--------------------+

Upvotes: 4

Related Questions