Re: [SQL] DISTINCT count(*), possible?

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(at)ludwig(dot)ucl(dot)ac(dot)uk |
+-------------------------+--------------------------------------+