Please someone help me with the following request.
I have list of data
SELECT * FROM DUMMY_DATA;
id value
------------
1 10
1 11
2 100
3 5
3 9
...
1000 1
1000 20
I need to group IDs into several ranged groups and calculate sum of IDs values for a range.
For instance
IDs_range sum_of_values
-----------------------------------------
0-10 (up to 10) xxxx
11-20 (up to 20) yyyy
...
1001 - 2000 (up to 2000) zzz
If I had not many ranges I would use CASE expression as described in the topic Oracle: how to “group by” over a range?
SELECT CASE
WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
ELSE '21+'
END AS age,
COUNT(*) AS n
FROM age
GROUP BY CASE
WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
ELSE '21+'
END
But I have a hundred of ranges and using CASE expression in this case is not so usable.
I know how to build a range table
WITH IDS_RANGES AS (
SELECT COLUMN_VALUE AS ID_RANGE FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(10,20,....,1000,2000)
)
SELECT ...
but I can’t figure out how to QUERY from both a range table(IDS_RANGES) and DUMMY_DATA table to build the desirable result.