Count query results on multi-join statements

select cnt.loginid, grp.last_name as 'Group Name' 
from contact cnt 
    right join grpmem list on cnt.contact_uuid = list.member 
    left join contact grp on grp.contact_uuid = list.group_id 
    join contact_acctyp cntacc on cnt.contact_uuid = cntacc.contact_uuid
where cntacc.c_acctyp_id in (select id from acctyp_v2 where sym like 'CDN%')

I have written a query for our system that pulls a list of all Can开发者_JS百科adian contacts and the group they are in.

Now, for people who are in multiple groups (their loginid appears multiple times) I need to determine the number of groups they are in (return a count). However, I am unsure of how to perform the count.

I'd like my output to be in the following format:

| USER ID | # of Groups |

I can't seem to figure out how to turn what I've written into that.

Assuming all you want to do is aggregate the information you are already getting back, and without looking in detail at your query, here is a guess:

select 
    cnt.loginid, 
    COUNT(*)
from contact cnt 
    right join grpmem list on cnt.contact_uuid = list.member 
    left join contact grp on grp.contact_uuid = list.group_id 
    join contact_acctyp cntacc on cnt.contact_uuid = cntacc.contact_uuid
where cntacc.c_acctyp_id in (select id from acctyp_v2 where sym like 'CDN%')
GROUP BY
     cnt.loginid