Count query results on multi-join statements

Question

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 Canadian 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.

Answer 1

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