Re: Problem with simple histogram query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 13 Aug 2003 06:40:38 GMT
Message-ID: <bhcmh6$vqml1$1@ID-82536.news.uni-berlin.de>

> 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.)
>

Knut, it can indeed be solved with an outer join, see below:

create table cd2 (
  minvalue number(16,2), 
  maxvalue number(16,2), 

  description varchar(30)
);

create table sc2 (uniformity number(16,2));

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.0);
insert into sc2 values (1.2);

insert into sc2 values (3.1);
insert into sc2 values (3.2);
insert into sc2 values (2.9);

insert into sc2 values (6.1);

select
  count(sc.uniformity) as occurrences,
  cd.description
from
  sc2 sc right join cd2 cd on
    sc.uniformity >= cd.minvalue and
    sc.uniformity < cd.maxvalue
group by
  cd.description;

drop table cd2;
drop table sc2;

I hope, you're on 9i, otherwise use the (+) thingie.

Hth,
Rene

-- 
  Rene Nyffenegger
  www.adp-gmbh.ch

Received on Wed Aug 13 2003 - 01:40:38 CDT