Re: [GENERAL] Any ideas why this doesn't work or how to rewrite it?

At 18:50 +0300 on 28/04/1999, Aaron Holtz wrote:

> db=> select count(distinct customer_username) from customerdata;
> ERROR: parser: parse error at or near "distinct"
>
> How do you get a count of distinct data output via postgres? I can always
> just count the number of tuples returned but this seemed to be a valid
> query.

Valid it is, but not yet supported in PostgreSQL.

An (ugly) workaround would be something along the lines of:

SELECT count(customer_username)
FROM customerdata c1
WHERE int( oid ) = (
SELECT min( int( c2.oid ) )
FROM customerdata c2
WHERE c1.customer_username = c2.customer_username
);

The WHERE clause causes only rows whose OIDs are the minimal for the
current username to be selected. Thus only one row is selected for each
username. And this is countable.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma