Help with WIDTH_BUCKET

From: LokalST <stbest001_at_hotmail.com>
Date: Wed, 28 May 2003 11:05:32 +0200
Message-ID: <bb1u30$46b$1_at_sunce.iskon.hr>

Hello,
[Quoted] [Quoted] 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 - 11:05:32 CEST