is a 'pairwise' possible / feasible in SQL? - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi, I have a table of the form

aid     cid
- ----   -----
1      123
2      456
3      667
3      879
3      123
4      878
4      456
4      123
5      999
5      667
5      879

My goal is to identify for each pair of cid values, the number of
times they have the same aid

Thus for example I would have

pair        count
- ----        -----
123 & 456   1
667 & 879   2
...

I currently do this by using a Python script to do a pairwise lookup, as

select count(aid) where cid = 123 and cid = 456;

but I was wondering whether I could construct a single SQL statement
to do this.

Any pointers would be appreciated,

Thanks,
- -------------------------------------------------------------------
Rajarshi Guha  <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C  DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
All great discoveries are made by mistake.
    -- Young


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkiXRYUACgkQZqGSLFHnnoTJJQCgtvromGcYfQVGsekGFQJU6vTo
oHgAnjpfKSkZR0MqBjdE6WFGO8SBr2WH
=zZJk
-----END PGP SIGNATURE-----