Counting 2 levels deep

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
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Counting 2 levels deep

Post by []InTeR[] »

Code: Select all

SELECT usergroup. * , count( usergroup_type.type_id ) num, count( user.id ) numdeb
FROM usergroup
LEFT JOIN usergroup_type ON usergroup.id = usergroup_type.usergroup_id
LEFT JOIN user ON user.type_id = usergroup_type.type_id
GROUP BY usergroup.id LIMIT 0, 50
What i now get is not correct, the 'num' is okay but the 'numdeb' not.

I want to count the number of user in a usergroup.
But this there is a link-table usergroup_type (links usergroup.id to user.type_id, many on many).

Does anyone know how you can count the number of user in a usergroup?

MySQL Version: 3.23.41
Post Reply