Re: SELECT syntax question - combining COUNT and DISTINCT
От | Louise Cofield |
---|---|
Тема | Re: SELECT syntax question - combining COUNT and DISTINCT |
Дата | |
Msg-id | 000d01c3882b$ce4622a0$7801a8c0@Louise обсуждение исходный текст |
Ответ на | SELECT syntax question - combining COUNT and DISTINCT (Cath Lawrence <Cath.Lawrence@anu.edu.au>) |
Список | pgsql-novice |
http://www.postgresql.org/docs/7.3/static/tutorial-agg.html :) Louise -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Cath Lawrence Sent: Wednesday, October 01, 2003 1:27 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] SELECT syntax question - combining COUNT and DISTINCT 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.Lawrence@anu.edu.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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
В списке pgsql-novice по дате отправления: