How to group numbers into ranges?

Requirement:

I have table with a numerical ID field. The values in this column may or may not be consecutive. For example

Data is

1, 2,3,4,5,8,9,10,15,16,17,18

Group should happen like –

Range1-5 count 5

 Range 6-7 count 0 because it’s missing

8-10 count 3

 11-14  0

 15-18 4

 

Test Table Design:

create table xxtest

(number1 number)

 

All numbers inserted in table:

Query:

SELECT   lb || '-' || ub RANGE, COUNT
    FROM (SELECT number1 + 1 AS lb,
                 LEAD (number1) OVER (ORDER BY number1) - 1 AS ub, 0 COUNT
            FROM xxtest)
   WHERE lb <= ub
UNION
SELECT   MIN (number1) || '-' || MAX (number1) number_range,
         MAX (number1) - MIN (number1) + 1 COUNT
    FROM (SELECT number1, number1 - ROW_NUMBER () OVER (ORDER BY number1) rn
            FROM xxtest)
GROUP BY rn
ORDER BY 1;

RANGE COUNT
1-5       5
11-14   0
15-18   4
6-7       0
8-10     3