UFN_FAFGROUP_GETCATEGORIZEDCOUNTANDAMOUNT

CREATE function dbo.UFN_FAFGROUP_GETCATEGORIZEDCOUNTANDAMOUNT()
returns table
as 
    return     
    select 
        top (select COUNT(*) from TEAMEXTENSION (nolock))
        GroupsAndMembers.GROUPID,    
        FR.[EVENTID],
        GIFTCOUNT_PARTICIPANTS = SUM(case when GroupsAndMembers.ISGROUP = 1 then 0 else isnull(FR.[GIFTCOUNT_ALL], 0) end),
        GIFTCOUNT_ONLINE = SUM(FR.[GIFTCOUNT_ONLINE]),
        GIFTCOUNT_OFFLINE = SUM(FR.[GIFTCOUNT_OFFLINE]),
        GIFTCOUNT_FROMEMAIL = SUM(FR.[GIFTCOUNT_FROMEMAIL]),
        GIFTCOUNT_FROMSOCIALMEDIA = SUM(FR.[GIFTCOUNT_FROMSOCIALMEDIA]),
        GIFTCOUNT_SELF = SUM(FR.[GIFTCOUNT_SELF]),
        AMOUNT_PARTICIPANTS = SUM(case when GroupsAndMembers.ISGROUP = 1 then 0 else isnull(FR.[AMOUNT_ALL], 0) end),
        AMOUNT_ONLINE = SUM(FR.[AMOUNT_ONLINE]),
        AMOUNT_OFFLINE = SUM(FR.[AMOUNT_OFFLINE]),
        AMOUNT_FROMEMAIL = SUM(FR.[AMOUNT_FROMEMAIL]),
        AMOUNT_FROMSOCIALMEDIA = SUM(FR.[AMOUNT_FROMSOCIALMEDIA]),
        AMOUNT_SELF = SUM(FR.[AMOUNT_SELF]),
        AMOUNT_PENDING = SUM(FR.[AMOUNTPENDING])

    from dbo.[FAFRAISEDTOTAL_CATEGORIZED] FR (nolock)
    inner join 
    (    select ISGROUP = 1, GROUPID = [PARENTID], [TEAMID], [CONSTITUENTID], [EVENTID] from dbo.[UFN_FAFGROUP_GETALLGROUPS]()
            union    
        select ISGROUP = 0, GROUPID = F.[PARENTID], F.[TEAMID], TF.[CONSTITUENTID], F.[EVENTID] from dbo.[UFN_FAFGROUP_GETALLGROUPS]() F
        inner join dbo.[TEAMFUNDRAISINGTEAMMEMBER] TFTM (nolock) on TFTM.[TEAMFUNDRAISINGTEAMID] = F.[TeamID]
        inner join dbo.[TEAMFUNDRAISER] TF (nolock) on TF.[ID] = TFTM.[TEAMFUNDRAISERID]
        group by F.[PARENTID], F.[TEAMID], F.[EVENTID], TF.[CONSTITUENTID]
    ) GroupsAndMembers
    on FR.[CONSTITUENTID] = GroupsAndMembers.[CONSTITUENTID] and FR.[EVENTID] = GroupsAndMembers.[EVENTID]   
    group by GroupsAndMembers.GROUPID, FR.[EVENTID]