Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with simple histogram query
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