From: | "Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | DISTINCT & COUNT |
Date: | 1999-11-26 16:35:17 |
Message-ID: | 002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
From | Date | Subject | |
---|---|---|---|
Next Message | Emils Klotins | 1999-11-26 17:48:02 | 6.5.3 - backend closes if wrong parameters to NULLIF, CASE |
Previous Message | Tom Lane | 1999-11-26 16:20:24 | Re: [SQL] Backend dies (6.5.3-1) on SELECT from 6 row table |