Re: Help with WIDTH_BUCKET

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 28 May 2003 06:52:37 -0700
Message-ID: <3ED4BF25.D9C848AC_at_exxesolutions.com>


LokalST wrote:

> 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;

Have you tried using CASE? With only time for a quick glance it seems to me it would be far eaiser to implement.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed May 28 2003 - 15:52:37 CEST

Original text of this message