pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (3+ messages)  latest in thread

Message-ID: <002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk>
Permalink:  ../002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk/
Also on:    postgresql.org/message-id/002b01bf382c$395bd7a0$760e01a3@oucs.ox.ac.uk

 · 

reply

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