Re: SELECT syntax question - combining COUNT and DISTINCT

On 1 Oct 2003 at 17:26, Cath Lawrence wrote:

Does this help?

select count(distinct pdb_id),chainid from chain group by chain_id;

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org