From: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
To: pgsql-sql@postgresql.org
Subject: DISTINCT & COUNT
Date: Fri, 26 Nov 1999 16:35:17 -0000
Message-ID: <002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk> (raw)
I'm sure there is an easy answer to this, but it's Friday evening
here...
I have table A, with field Cid, and table C, whose primary key is Cid.
I have table B which contains a subset of the IDs of table A, Aid.
I need to produce a *count* of items in C for which certain criteria
in A are true, among them that only items in both B and A can be
considered (equijoin B to A).
At the moment, I am doing
CREATE TABLE C (Cid int4 primary key,cname text);
INSERT INTO C VALUES (1,'I am 1');
INSERT INTO C VALUES (2,'I am 2');
INSERT INTO C VALUES (3,'I am 3');
CREATE TABLE A (Aid int4 primary key,somefield int4,otherfield
int4,Cid int4 not null);
INSERT INTO A VALUES(1,1,2,1);
INSERT INTO A VALUES(2,1,3,1);
INSERT INTO A VALUES(3,1,2,1);
INSERT INTO A VALUES(4,1,2,2);
INSERT INTO A VALUES(5,1,3,2);
INSERT INTO A VALUES(6,1,2,3);
INSERT INTO A VALUES(7,1,2,3);
CREATE TABLE B (Aid int4,Bid int4 primary key);
INSERT INTO B VALUES (1,1);
INSERT INTO B VALUES (2,2);
INSERT INTO B VALUES (3,3);
INSERT INTO B VALUES (4,4);
INSERT INTO B VALUES (5,5);
SELECT Count(Cid) FROM C WHERE EXISTS(SELECT A.Aid FROM A,B WHERE
A.Cid=C.Cid AND B.Aid=A.Aid AND A.somefield=1 AND A.otherfield=2);
This gives the correct answer, i.e.2.
There must be something more elegant (and faster), but my experiments
with COUNT, WHERE and DISTINCT were not a success.
Thanks,
Moray
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: moray.mcconnachie@computing-services.oxford.ac.uk
Subject: Re: DISTINCT & COUNT
In-Reply-To: <002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk>
References: <002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox