Received: from oxmail.ox.ac.uk (oxmail3.ox.ac.uk [163.1.2.9]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA29564 for ; Fri, 26 Nov 1999 11:35:23 -0500 (EST) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 11rOLA-00055s-00 for pgsql-sql@postgresql.org; Fri, 26 Nov 1999 16:35:20 +0000 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 2.12 #1) id 11rOLA-0005ih-00 for pgsql-sql@postgreSQL.org; Fri, 26 Nov 1999 16:35:20 +0000 Message-ID: <002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: Subject: DISTINCT & COUNT Date: Fri, 26 Nov 1999 16:35:17 -0000 Organization: Oxford University MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 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