Page 1 of 1

trouble with a mysql query

Posted: Mon Jun 09, 2003 5:09 pm
by penguinboy
I'm having a bit of trouble.

I have a Mysql table with 4 fields:

id
userid
groupid
subgroupid

I want to find the number of unique-users within a subgroup.

normmaly I would do something like :

Code: Select all

<?php
$sql = mysql_query("Select * from my_table where subgroup='".$id."'");
print mysql_num_rows($sql);
?>
I can have multiple occurrences where a subgroupid and userid are the same, because subgroups can be refrecenced to multiple groups.

so I could have

id=1, userid=1, subgroupid=1, groupid=1
id=2, userid=1, subgroupid=1, groupid=2
id=3, userid=1, subgroupid=1, groupid=3
id=4, userid=2, subgroupid=1, groupid=1
id=5, userid=3, subgroupid=1, groupid=3



In my query that would count as 5 rows, but I only want it to count as 3.
Basically I want to be able to say "3 users are associated to subgroup 1".

Is there anyway I can do that?

--
thanks for any help

--pb

DISTINCT

Posted: Mon Jun 09, 2003 5:29 pm
by phpScott

Code: Select all

SELECT DISTINCT userId FROM myTable WHERE subgroup=$someSubGroup
http://www.mysql.com/doc/en/DISTINCT_optimisation.html
phpScott