Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.2/8.9.1) with ESMTP id MAA25502 for ; Wed, 10 Mar 1999 12:04:07 -0500 (EST) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Wed, 10 Mar 1999 17:03:41 +0000 Received: from [128.40.242.190] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id RAA24284; Wed, 10 Mar 1999 17:06:26 GMT X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 10 Mar 1999 17:01:15 +0000 To: pgsql-sql@postgresql.org From: Stuart Rison Subject: DISTINCT within aggregates. Hi, I have PG 6.4 which does not appear to support DISTINCT with aggregates. Here's an example with the following table: key|numero ---+----- 1|one 2|two 3|three 4|one 4|four 2|one (6 rows) brecard10=> select count(numero) from test; count ----- 6 (1 row) brecard10=> select count(numero) from test group by value; count ----- 1 3 1 1 (4 rows) brecard10=> select count(distinct numero) from test group by value; ERROR: parser: Syntax error at or near "distinct" I want to be able to count how many DISTINCT 'numero' entries there are in test (i.e 4). The first statement is a simple count. The second uses a group which does indeed tell us that there are four different 'numero's but does not return a unique count. The last statement is the statement to use (at least according to my SQL handbook - Bowman et al.) but is not supported by PG 6.4. Has anybody found a workaround? cheers, Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+