#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    20
    Rep Power
    0
    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.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    1
    Rep Power
    0
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    20
    Rep Power
    0
    Aha! That was it. Thanks much.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    16
    <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.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    20
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    16
    <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

Similar Threads

  1. Replies: 0
    Last Post: January 3rd, 2004, 09:33 AM
  2. Is it better to declare a column NULL or NOT NULL
    By lunatic in forum MySQL Help
    Replies: 6
    Last Post: October 22nd, 2003, 04:12 AM
  3. Debugging column logic
    By tlush in forum PHP Development
    Replies: 2
    Last Post: March 13th, 2003, 04:47 PM
  4. Questions about column types
    By kixx in forum MySQL Help
    Replies: 3
    Last Post: March 10th, 2003, 07:12 PM
  5. SET column type questions
    By JMM in forum MySQL Help
    Replies: 1
    Last Post: August 2nd, 2001, 05:05 PM

IMN logo majestic logo threadwatch logo seochat tools logo