Any ideas why this doesn't work or how to rewrite it?

On Wed, 28 Apr 1999, Aaron Holtz wrote:

This seems like a simple SQL command, but I'm getting errors.
Running 6.5.0 under RedHat 5.2:

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.

In SQL, it is just pretty valid query. But Postgres does not implement
SELECT COUNT(DISTINCT).

Do instead
SELECT DISTINCT(customer_username) FROM customerdata;
and count it in your program.

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Specialist
Email: aholtz@bright.net
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------

Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.

I believe its:
select DISTINCT count(*) from customerdata;

of course you are welcome to replace the "*" with a field name, but why
bother typing all those extra keystrokes :)

-----Original Message-----
From: Aaron Holtz <aholtz@bright.net>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Wednesday, April 28, 1999 10:44 AM
Subject: [GENERAL] Any ideas why this doesn't work or how to rewrite it?

I thought that as well, but that is counting the distinct number of rows
returned from count(*) -> hence you'll always get the number of rows in
your database or the number of entries in whatever field you just
counted..... Thanks for the thoughts, though!

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Specialist
Email: aholtz@bright.net
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------

On Apr 28, K.T. molded the electrons to say....

Yes, that is what I'll have to do. Just counting the number of tuples
returned from this query would work. I was just wondering why this wasn't
implemented in postgres or what the valid syntax would be. Thanks!

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Specialist
Email: aholtz@bright.net
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------

On Apr 28, Oleg Broytmann molded the electrons to say....

This seems like a simple SQL command, but I'm getting errors.
Running 6.5.0 under RedHat 5.2:

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?

SELECT customer_username, COUNT(customer_username) FROM customerdata GROUP BY customer_username;

Hey there...

SELECT customer_username, COUNT(customer_username) FROM customerdata GROUP BY customer_username;

The above query, in effect, does the same thing that:

SELECT DISTINCT(customer_username) from customerdata

does. In order to get the total number of distinct customer_usernames,
you would still have to count the rows returned (which is easily
enough done with PHP $count = pg_NumRows($query_result)).

My guess is that SELECT DISTINCT might even be a bit quicker...?? (gurus)

Bob

Yes, I'm just taking the value ntuples returned after doing:

select distinct customer_username from customerdata group by
customer_username;

I guess my big thing was that I saw a couple of

select count(distinct something) from table;

examples in an SQL book that I have. Was just curious as to whether psql
didn't support this type of command or decided that a better way existed
to write the query. Thanks to all for the thoughts and ideas. Maybe this
support will be added at some juncture.

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Specialist
Email: aholtz@bright.net
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------

On Wed, 28 Apr 1999, Bob Dusek wrote:

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

At 21:04 +0300 on 29/04/1999, Brett W. McCoy wrote:

I think, Aaron, you could get a count of distinct customer names like this:

SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
GROUP BY customer_username;

This will give you 2 columns, one with the distinct customer_usernames
and the second with the count of each. The GROUP BY caluse is important
here. This looks like what you wanted in your original query.

No, Brett. COUNT( DISTINCT ...) is supposed to count the number of distinct
names in a table. Here, I created a test table:

testing=> select * from test;
customer
--------
moshe
david
hanna
david
sarah
moshe
suzanne
moshe
moshe
(9 rows)

The distinct names are:

testing=> select distinct customer
testing-> from test;
customer
--------
david
hanna
moshe
sarah
suzanne
(5 rows)

So clearly, the datum he wanted was "5" - there are five distinct customers
here.

Your query, however, gives the following:

testing=> select distinct customer, count(*)
testing-> from test
testing-> group by customer;
customer|count
--------+-----
david | 2
hanna | 1
moshe | 4
sarah | 1
suzanne | 1
(5 rows)

Which shows him the number of REPETITIONS on each distinct name.

My ugly query gives:

testing=> select count(*)
testing-> from test t1
testing-> where int( oid ) = (
testing-> SELECT min( int( t2.oid ) )
testing-> FROM test t2
testing-> WHERE t2.customer = t1.customer
testing-> );
count
-----
5
(1 row)

And this is the exact number of distinct names in the table.

Herouth

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

On Thu, 29 Apr 1999, Herouth Maoz wrote:

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
);

I think, Aaron, you could get a count of distinct customer names like this:

SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
GROUP BY customer_username;

This will give you 2 columns, one with the distinct customer_usernames
and the second with the count of each. The GROUP BY caluse is important
here. This looks like what you wanted in your original query.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Schapiro's Explanation:
The grass is always greener on the other side -- but that's
because they use more manure.