using COUNT(DISTINCT column)

I am attempting to count the number of distinct users in a table where a user can be expected to have multiple records.

Here's what I did:

mysql> select distinct user from CE_TRACKER;
+-----------+
| user |
+-----------+
| david |
| dkaris |
| eel |
| patsi |
| testeel |
| testeel33 |
| testeel37 |
+-----------+
7 rows in set (0.01 sec)

mysql> select count(distinct user) from CE_TRACKER;
ERROR 1064: You have an error in your SQL syntax near 'distinct user) from CE_TRACKER' at line 1

The example given in the documentation exactly matches what I did here:

COUNT(DISTINCT expr,[expr...])
Returns a count of the number of different values.

mysql> select COUNT(DISTINCT results) from student;

Where oh where have I gone wrong? Thanks for any help.

As of MySQL 3.23.2 you are able to use the command you used "mysql> select count(distinct user) from CE_TRACKER;" . Anything before doesn't allow you to use distinct with count as you did. I hope that helps you.

Aha! That was it. Thanks much.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by elin:
Aha! That was it. Thanks much.[/quote]

Until that new version, why don't you use:

select count(user) from CE_TRACKER group by user;

It should give you the same results.

It doesn't. That gives me the number of rows belonging to each distinct user, but doesn't count the number of users having rows.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by elin:
It doesn't. That gives me the number of rows belonging to each distinct user, but doesn't count the number of users having rows.[/quote]

You're so right, I had a bit of a black-out
So until the next version there's nothing left to do just a select distinct user from CE_TRACKER; and then count the number of returned rows (or do a $mysql_num_rows in PHP)

Nice cracker, I liked it!

Peter