Arithmetic overflow error converting expression to data type int message in T-SQL code

Question

I have the following code:

     SELECT 
            ehrprg.ReportName 
           ,ehrprg.AnnualGoalServiceMinutes
           ,COUNT(DISTINCT ct.[ClientFK]) AS [UnduplicatedClients]

    FROM 
           [WH].[Fact].[EHRClinicalTransaction] ct
    INNER JOIN [Dimension].EHRProgram ehrprg ON 
                           ct.ProgramFK = ehrprg.WHID

    WHERE
          ehrprg.AnnualGoalServiceMinutes > 0

    GROUP BY
            ehrprg.ReportName 
           ,ehrprg.AnnualGoalServiceMinutes 

    ORDER BY
           ReportName 

and result:

But I need it would have only one 'SM NV'(instead of 2) and one 'SM REACH' (instead of 3) rows in the [ReportName] column, summarizing [ServiceMinutes]

When I use SUM(ehrprg.AnnualGoalServiceMinutes) it gives me "Arithmetic overflow error converting to datatype int" error.

Then, I tried ,SUM(CONVERT(BIGINT, ehrprg.AnnualGoalServiceMinutes)), but getting the following:

It is still not grouping (not summarizing AnnualGoalServiceMinutes) and gives some values, I can't understand

My goal is to see instead of-

        ReportName   AnnualGoalServiceMin
        SM NV        197885
        SM NV        348654

        SM REACH     40000 
        SM REACH     80000
        SM REACH     380000

I expect the SUM of the AnnualGoalServiceMin:

       ReportName   AnnualGoalServiceMin
       SM NV        546539
       SN REACH     500000           

Please, help

Answer 1

I don't know the source of the overflow error (it should not be happening based on the magnitude of the integers invovled), but I think what you need here is a second level of aggregation:

WITH cte AS (
    SELECT
        ehrprg.ReportName,
        ehrprg.AnnualGoalServiceMinutes,
        COUNT(DISTINCT ct.[ClientFK]) AS UnduplicatedClients
   FROM [WH].[Fact].[EHRClinicalTransaction] ct
   INNER JOIN [Dimension].EHRProgram ehrprg
        ON ct.ProgramFK = ehrprg.WHID
   WHERE ehrprg.AnnualGoalServiceMinutes > 0
   GROUP BY ehrprg.ReportName, ehrprg.AnnualGoalServiceMinutes
)

SELECT
    ReportName,
    SUM(AnnualGoalServiceMinutes) AS AnnualGoalServiceMinutes,
    SUM(UnduplicatedClients) AS UnduplicatedClients
FROM cte
GROUP BY
    ReportName;