On 2:08 pm 08/04/08 Rajarshi Guha <rguha@indiana.edu> wrote:
> pair count
> - ---- -----
> 123 & 456 1
> 667 & 879 2
create temp table aic_cid
(
id smallint,
cid smallint
);
insert into aic_cid values (1,123);
insert into aic_cid values (2,456);
insert into aic_cid values (3,667);
insert into aic_cid values (3,879);
insert into aic_cid values (3,123);
insert into aic_cid values (4,878);
insert into aic_cid values (4,456);
insert into aic_cid values (4,123);
insert into aic_cid values (5,999);
insert into aic_cid values (5,667);
insert into aic_cid values (5,879);
select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join
aic_cid b on a.cid <>b.cid and a.id = b.id where b.cid is not null group by
a.cid, b.cid order by a.cid;
ac | bc | count
-----+-----+-------
123 | 456 | 1
123 | 667 | 1
123 | 878 | 1
123 | 879 | 1
456 | 123 | 1
456 | 878 | 1
667 | 123 | 1
667 | 879 | 2
667 | 999 | 1
878 | 123 | 1
878 | 456 | 1
879 | 123 | 1
879 | 667 | 2
879 | 999 | 1
999 | 667 | 1
999 | 879 | 1
Is that what you are looking for?