Re: Help with WIDTH_BUCKET

This is one way of doing it:

with t1 as (select id, age, WIDTH_BUCKET(age,20,60,4) grp, rownum rn from table1) select sum(cnt),grp from (select grp, 1 cnt from t1 union all (select rn as grp, 0 cnt from t1 where rn < 6)) group by grp;

  SUM(CNT) GRP
---------- ----------

         3          0
         4          1
         0          2
         2          3
         2          4
         1          5

6 rows selected.

SQL>

• Jusung Yang

"LokalST" <stbest001_at_hotmail.com> wrote in message news:<bb1u30$46b$1_at_sunce.iskon.hr>...
> Hello,
> I have to make a report which will display the number of items in given
> interval (and the number of intervals is given also).
> So, I decide to use a new function WIDTH_BUCKET and it works just fine.
> Problem is that if no items are in one of the intervals than it will be no
> displayed.
>
> Example:
> Data
> ID Age
> 1 19
> 2 20
> 3 22
> 4 25
> 5 18
> 6 59
> 7 43
> 8 49
> 9 52
> 10 29
> 11 19
> 12 62
>
> I have for example 4 intervals and the range is 20 - 60. The result should
> be following:
> Cnt Interval No. Age range
> ---------------------
> 3 0 <20
> 4 1 20<= Age<30
> 0 2 30<= Age<40
> 2 3 40<= Age<50
> 2 4 50<= Age<60
> 1 5 >=60
>
> So i've tried with following query:
>
> SELECT Count(*) OVER (PARTITION BY WIDTH_BUCKET(age,20,60,4)) As Cnt,
> WIDTH_BUCKET(age,20,60,4) As Interval_No
> FROM table1
>
> But i don't get the desired result.
>
> Anyone please help.
>
> Tnx in advance.
>
> Here are the inserts for data:
> create table TABLE1
> (
> ID NUMBER not null,
> AGE NUMBER not null
> );
> alter table TABLE1
> add constraint ID_PK primary key (ID);
>
> prompt Disabling triggers for TABLE1...
> alter table TABLE1 disable all triggers;
> prompt Loading TABLE1...
> insert into TABLE1 (ID, AGE)
> values (1, 19);
> insert into TABLE1 (ID, AGE)
> values (2, 20);
> insert into TABLE1 (ID, AGE)
> values (3, 22);
> insert into TABLE1 (ID, AGE)
> values (4, 25);
> insert into TABLE1 (ID, AGE)
> values (5, 18);
> insert into TABLE1 (ID, AGE)
> values (6, 59);
> insert into TABLE1 (ID, AGE)
> values (7, 43);
> insert into TABLE1 (ID, AGE)
> values (8, 49);
> insert into TABLE1 (ID, AGE)
> values (9, 52);
> insert into TABLE1 (ID, AGE)
> values (10, 29);
> insert into TABLE1 (ID, AGE)
> values (11, 19);
> insert into TABLE1 (ID, AGE)
> values (12, 62);
> commit;
Received on Wed May 28 2003 - 20:36:12 CEST