UFN_FAFGROUP_GETGROUPANDMEMBERCOUNT

create function dbo.UFN_FAFGROUP_GETGROUPANDMEMBERCOUNT()
returns table
as 
    return 
    select A.ParentID as GROUPID, A.EventID, SUM(ISNULL(A.CHILDGROUPSCNT,0)) as CHILDGROUPSCNT, SUM(ISNULL(A.PARTICIPANTS,0)) as PARTICIPANTS from 
    (  
      select F.ParentID, count(distinct TFT.ID) as CHILDGROUPSCNT, count(distinct TF.CONSTITUENTID) as PARTICIPANTS, F.EventID 
        from dbo.UFN_FAFGROUP_GETALLGROUPS() F
        left join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.PARENTTEAMID = F.TeamID
        left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFTM.TEAMFUNDRAISINGTEAMID = F.TeamID
        left join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
        group by f.EventID, f.ParentID
     ) A
    group by A.EventID, A.ParentID