MySql count() in conjunction with distinct

how to use count() with "distinct" in the same mysql statement

[Mitch888]

hi to all,

I have a table called "topay_list" and it contains a field called "user_id". I need to know the number of UNIQUE user_id in the table.

the count(user_id) gives me all the user_id's including repeating ones. How can I use the below statement in conjunction with "distinct"?

$sql2="select Count(user_id) from topay_list where sb_club='$i2[0]'";

thank you

[jatar_k]

why not try a php mysql combo?

$sql = "select distinct user_id from topay_list where sb_club='$i2[0]'";
$rows = mysql_query($sql);
$numrows = mysql_num_rows [php.net]($rows);

[Mitch888]

worked like a charm

thanks greatly appreciate it

[xunker]

Additionally, you can get the count of the times each distinct value is in the table with something hairy like:

SELECT DISTICT user_id, COUNT(user_id) AS sum FROM topay_list WHERE sb_club='$i2[0]' GROUP BY user_id ORDER BY sum;

...which will return lines containing the user_id and the times it occurs in the table (just a warning, if the table is big this can take time to execute)

[Mitch888]

thanks, you just saved me couple of steps in coding. Good stuff