It is an ugly solution I would not personally use in Oracle, though as you say you cannot use sub-querys, I know of on better method.

Also, in my example you do not need to use the 'distinct'...

Hello,

The reason that your query does not work correctly is because by using <> in this manner you are creating a 'partial' Cartesian Join between the two tables. In doing so the query returns...

Try:

select g.name, count(1)
from group g
where g.id in (select u.groupid from user u where u.userid = <put_userid_here>)
group by g.name