On 2:08 pm 08/04/08 Rajarshi Guha <rguha@xxxxxxxxxxx> 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?
