pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: pgsql-sql@postgresql.org
To: matthew@venux.net
Subject: Re: [SQL] DISTINCT count(*), possible?
Date: Wed, 16 Jun 1999 09:55:37 +0100
Message-ID: <v04020a00b38d1337fb45@[128.40.242.190]> (raw)

Postgres is behaving as expected...

>Can someone tell me how I can get this to work?
>
>select DISTINCT count(address1) from aro;
>
>returns 240, which is wrong.

It's not the answer you were expecting but it's not wrong.  The DISTINCT
means that, of all the row returned by your query, only one instance of
multiple identical tuples (rows) will be returned.

Here your query returns just one row (a count of the total number of rows
in aro), which by definition is unique, and so DISTINCT is redundant.

>But this:
>select DISTINCT address1 from aro;
>
>returns 219, which is exactally what I am looking for, minus the query
>output.

Yes, this time the query is returning all the rows in aro and the DISTINCT
is 'removing' all the duplicate addresses so you get a final count of the
unique addresses... but you also get all the returned query output!

>I don't need the tuples, just a count of distinct addresses.

What you want is the standard SQL:

SELECT count(DISTINCT address1) from aro

First find all unique instances of address1 then return just their count.

Saddly, this is not supported by postgreSQL (yet).

A previously posted work-around (search the GENERAL mailing list with
'count', 'DISTINCT' and 'Herouth') adapted to your query is:

SELECT count(*)
FROM aro t1
WHERE int( oid ) = (
	SELECT min( int( t2.oid ) )
	FROM test t2
	WHERE t2.address1 = t1.address1
);

It's inelegant (because of course, count(DISTINCT) is the 'good' way of
doing things) but it works!

HTH,

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              |
+-------------------------+--------------------------------------+



view thread (8+ messages)  latest in thread

Message-ID: <v04020a00b38d1337fb45@[128.40.242.190]>
Permalink:  ../v04020a00b38d1337fb45@%5B128.40.242.190%5D/
Also on:    postgresql.org/message-id/v04020a00b38d1337fb45@[128.40.242.190]

 · 

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: stuart@ludwig.ucl.ac.uk, matthew@venux.net
  Subject: Re: [SQL] DISTINCT count(*), possible?
  In-Reply-To: <v04020a00b38d1337fb45@[128.40.242.190]>
  References: <v04020a00b38d1337fb45@[128.40.242.190]>

* 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