select count(sc.uniformity) as occurrences, cd.description
from sc2 sc RIGHT OUTER JOIN cd2 cd
ON sc.uniformity >= cd.minvalue
and sc.uniformity < cd.maxvalue
group by cd.description
"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:37:40 CDT
