Horton

Thread

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

    Aaron Holtz <aholtz@bright.net> — 1999-04-28T15:50:36Z

    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.

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

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

    Oleg Broytmann <phd@sun.med.ru> — 1999-04-28T16:00:04Z

    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.

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

    Aaron Holtz <aholtz@bright.net> — 1999-04-28T17:08:41Z

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

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

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

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-04-29T14:44:41Z

    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

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

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-04-29T15:09:09Z

    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

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

    Brett W. McCoy <bmccoy@lan2wan.com> — 1999-04-29T18:04:45Z

    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.