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

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