From: | Cath Lawrence <Cath(dot)Lawrence(at)anu(dot)edu(dot)au> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | SELECT syntax question - combining COUNT and DISTINCT |
Date: | 2003-10-01 07:26:55 |
Message-ID: | A2378F82-F3E0-11D7-B931-00039390F614@anu.edu.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I'm having some trouble working with aggregates. I think I'm missing
the concept of how aggregates are treated. Is there a tutorial page?
Here's my problem, no doubt very simple. It's one table only, nice and
easy...
SELECT DISTINCT pdb_id FROM chain WHERE chain_id = 'A';
But now I want to count how many records are returned and I can't work
it out. I'm sure I'm missing something obvious.
For instance "SELECT COUNT(pdb_id) FROM chain WHERE chain_id = 'A';"
works fine but of course is no longer distinct.
A secondary question (relating to my quality control problem but not
the subject heading) is why I can't do this:
"SELECT DISTINCT chain_id FROM chain WHERE pdb_id IN (SELECT pdb_code
FROM pdb_entry WHERE chain_count=1);"
This looks OK but takes apparently forever (or until I lose patience)
on my Mac G4. There are about 10,000 values in that subquery, so I
didn't expect it to be lightning fast, but it seems ridiculous.
Relevant snippets of table:
pdb_entry - 19,587 entries
---------
pdb_code is KEY, CHAR(4), unique, indexed
chain_count is INTEGER
chain - 40,844 entries
-----
pdb_id is CHAR(4) Foreign key, indexed
chain_id is CHAR(1)
Cath Lawrence, Cath(dot)Lawrence(at)anu(dot)edu(dot)au
Senior Scientific Programmer, Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4088)
Australian National University, Canberra ACT 0200
ph: (02) 61257959 mobile: 0421-902694 fax: (02) 61252595
From | Date | Subject | |
---|---|---|---|
Next Message | paul | 2003-10-01 08:55:50 | Re: SELECT syntax question - combining COUNT and DISTINCT |
Previous Message | Tom Lane | 2003-09-29 22:18:45 | Re: Install question |