Re: Problem with simple histogram query

Hello,

Thanks, that seems to be a useful function, and I've done some small tests. But we might not always want equal interval size, and in that case it's more flexible to get the bin limits form the database in a query.

But thanks anyway, we might get back to this if we can't get the original query to work.

Regards,

Knut

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0308120915.7703813b_at_posting.google.com...
> Take a look of the WIDTH_BUCKET function. Might easier to do it that
way...
>
>
> - Jusung Yang
>
>
> "Knut Hovda" <knut.hovda_at_pti.no> wrote in message
news:<7N6_a.1487$dbb.185756160_at_news.telia.no>...
> > Hello,
> >
> > We have a problem with a query to create a histogram (counting
occurrences
> > in categories). Consider the following simplified example:
> >
> > create table cd2 (minvalue number(16), maxvalue number(16), description
> > varchar(30));
> > create table sc2 (uniformity number(16));
> > insert into cd2 values (0,2,'small');
> > insert into cd2 values (2,4,'medium');
> > insert into cd2 values (4,6,'large');
> > insert into sc2 values (1);
> > insert into sc2 values (3);
> >
> > select count(*) as occurrences, cd.description
> > from sc2 sc, cd2 cd
> > where sc.uniformity >= cd.minvalue
> > and sc.uniformity < cd.maxvalue
> > group by cd.description;
> >
> > gives
> >
> > OCCURRENCES DESCRIPTION
> > ----------- --------------
> > 1 medium
> > 1 small
> >
> > But note that the 'large' category is not displaying, since no rows in
sc2
> > fall in this category . We want the result to display (sorting order is
not
> > relevant):
> >
> > 0 large
> > 1 medium
> > 1 small
> >
> > How can we achieve this? (We first thought this was a simple matter of
an
> > outer join missing, but somehow we seem to miss the trick here.)
> >
> > In advance, thanks for your help.
> >
> > Regards,
> >
> > Knut
Received on Wed Aug 13 2003 - 06:16:58 CDT