Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with simple histogram query

Re: Problem with simple histogram query

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 12 Aug 2003 10:15:16 -0700
Message-ID: <130ba93a.0308120915.7703813b@posting.google.com>


Take a look of the WIDTH_BUCKET function. Might easier to do it that way...

"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 Tue Aug 12 2003 - 12:15:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US