Use of SUM and COUNT with GROUP BY

Hi,

check if one of those option fit your needs:

select [State],[Desc], Category,count(distinct [R]),SUM(M)
from (
        select 
                [State],[Desc], Category, [MemberID],[Code], DATEDIFF(MONTH,StartDt,EndDt)+1 M, RANK() over (order by [MemberID],[Code]) R
        from T
) T_In
group by [State],[Desc], Category
GO

select [State],[Desc], Category,count(distinct [R]),SUM( distinct M)
from (
        select 
                [State],[Desc], Category, [MemberID],[Code], DATEDIFF(MONTH,StartDt,EndDt)+1 M, RANK() over (order by [MemberID],[Code]) R
        from T
) T_In
group by [State],[Desc], Category
GO

---

[Personal Site] [Blog] [Facebook]