trouble with a mysql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

trouble with a mysql query

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

DISTINCT

Post by phpScott »

Code: Select all

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