Install scripts:drop table INTERVALS/create table INTERVALS(NAME varchar2(100), sal number(10,2))/insert into intervals(NAME,sal)select object_name, dbms_random.value(0,100) from all_objects/commit//*********************************************************************************************/Case scenario 1 - using on-the-fly pivot tableset pagesize 200set linesize 200with xas(select min(sal) mn, max(sal) mx from intervals)select (l-1)*(mx-mn)/10 start_value, (l)*(mx-mn)/10 end_value, count(1) no_of_values from intervals i,(select mn, mx from x),(select level l from dual connect by level <= 10) where sal>(case when l=1 then mn-0.001 else (l-1)*(mx-mn)/10 end) and sal<=(l)*(mx-mn)/10 group by (l-1)*(mx-mn)/10 , (l)*(mx-mn)/10//*********************************************************************************************/Case scenario 2 - using width_bucket analytical functionset pagesize 200set linesize 200select (int_no-1)*(end_int-start_int)/10 start_value,(int_no)*(end_int-start_int)/10 end_value, count(1) no_of_values from(select name, sal, width_bucket(sal,0,100.001,10) int_no, min(sal) over (order by sal asc) start_int, max(sal) over (order by sal desc) end_int from intervals)group by int_no, start_int, end_int//*********************************************************************************************/
Output 1:START_VALUE END_VALUE NO_OF_VALUES----------- ---------- ------------ 0 10 5061 10 20 5126 20 30 5195 30 40 5136 40 50 5058 50 60 5173 60 70 5221 70 80 5028 80 90 5053 90 100 5018/*******************************************************************/Output 2:START_VALUE END_VALUE NO_OF_VALUES----------- ---------- ------------ 0 10 5061 10 20 5126 20 30 5195 30 40 5136 40 50 5058 50 60 5173 60 70 5221 70 80 5028 80 90 5053 90 100 5018/*******************************************************************/
