[SOLVED] How to count "total unique values" of a field in a table?

[adrianTNT]

Hello, can someone tell if there is a commend for this?

 

I have a table that has fields like:

 

ID        CATEGORY ID        USER ID

-----+----------------+-------------

0                10                25

1                12                24

2                9               24

3                20                25

 

(The table represents users subscribed to categories).

User id can be more than once in the table, I want to find the total number of users subscribed to categories, in the above sample this total would be 2 (user 24 and user 25).

I think the command (if exists) should be something like: "count total unique values of USER ID from TABLE_NAME" ?

 

Any ideas?

Thanks.

[PHP_PhREEEk]

Use SUM and DISTINCT in any way you want to look at your data.

 

PhREEEk

[rajivgonsalves]

something like this should work

 

select count(DISTINCT userid) from table

 

[adrianTNT]

I managed to do it by count(DISTINCT user_id) FROM table_name, it returns correct results but it looks buggy to me, is this correct?

 

// total unique users subscribed to at least one category 
mysql_select_db($database_cms, $cms);
$query_Recordset_stats_unique_subscribers = "SELECT count(DISTINCT cat_subscription_user_id) FROM cat_subscriptions";
$Recordset_stats_unique_subscribers = mysql_query($query_Recordset_stats_unique_subscribers, $cms) or die(mysql_error());
$row_Recordset_stats_unique_subscribers = mysql_fetch_assoc($Recordset_stats_unique_subscribers);
$totalRows_Recordset_stats_unique_subscribers = mysql_num_rows($Recordset_stats_unique_subscribers);

 

I have "count" there in 2nd line but in order to return the total is this line correct? Do I use count distinct again?

 

<?php echo $row_Recordset_stats_unique_subscribers['count(DISTINCT cat_subscription_user_id)']; ?>

 

It is the second code that looks buggy, I am not sure the thing inside the [] is correct, it does return the correct results but I was expecting it to return the total by: $totalRows_Recordset_stats_unique_subscribers

Is it correct?

[rajivgonsalves]

you can make it simpler by using the following code

 

// total unique users subscribed to at least one category 
mysql_select_db($database_cms, $cms);
$query_Recordset_stats_unique_subscribers = "SELECT count(DISTINCT cat_subscription_user_id) as count FROM cat_subscriptions";
$Recordset_stats_unique_subscribers = mysql_query($query_Recordset_stats_unique_subscribers, $cms) or die(mysql_error());
$row_Recordset_stats_unique_subscribers = mysql_fetch_assoc($Recordset_stats_unique_subscribers);
$totalRows_Recordset_stats_unique_subscribers = mysql_num_rows($Recordset_stats_unique_subscribers);

 

and then

 

<?php echo $row_Recordset_stats_unique_subscribers['count']; ?>

[adrianTNT]

That worked.

Thank you very much Rajiv.