Sql-server – Use Distinct With Sum

This construction is not currently supported in SQL Server. It could (and should, in my opinion) be implemented in a future version.

Applying one of the workarounds listed in the feedback item reporting this deficiency, your query could be rewritten as:

WITH UpdateSet AS
(
    SELECT 
        AgentID, 
        RuleID, 
        Received, 
        Calc = SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY AgentID, RuleID) 
    FROM 
    (
        SELECT  
            AgentID,
            RuleID,
            Received,
            rn = ROW_NUMBER() OVER (
                PARTITION BY AgentID, RuleID, GroupID 
                ORDER BY GroupID)
        FROM    #TempTable
        WHERE   Passed = 1
    ) AS X
)
UPDATE UpdateSet
SET Received = Calc;

The resulting execution plan is:

This has the advantage of avoiding an Eager Table Spool for Halloween Protection (due to the self-join), but it introduces a sort (for the window) and an often-inefficient Lazy Table Spool construction to calculate and apply the SUM OVER (PARTITION BY) result to all rows in the window. How it performs in practice is an exercise only you can perform.

The overall approach is a difficult one to make perform well. Applying updates (especially ones based on a self-join) recursively to a large structure may be good for debugging but it is a recipe for poor performance. Repeated large scans, memory spills, and Halloween issues are just some of the issues. Indexing and (more) temporary tables can help, but very careful analysis is needed especially if the index is updated by other statements in the process (maintaining indexes affects query plan choices and adds I/O).

Ultimately, solving the underlying problem would make for interesting consultancy work, but it is too much for this site. I hope this answer addresses the surface question though.

Alternative interpretation of the original query (results in updating more rows):

WITH UpdateSet AS
(
    SELECT 
        AgentID, 
        RuleID, 
        Received, 
        Calc = SUM(CASE WHEN Passed = 1 AND rn = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY AgentID, RuleID) 
    FROM 
    (
        SELECT  
            AgentID,
            RuleID,
            Received,
            Passed,
            rn = ROW_NUMBER() OVER (
                PARTITION BY AgentID, RuleID, Passed, GroupID
                ORDER BY GroupID)
        FROM    #TempTable
    ) AS X
)
UPDATE UpdateSet
SET Received = Calc
WHERE Calc > 0;

Note: eliminating the sort (e.g. by providing an index) might reintroduce the need for an Eager Spool or something else to provide the necessary Halloween Protection. Sort is a blocking operator, so it provides full phase separation.

The accepted answer doesn't show the 2016 row, which I would expect to be 0 based on the initial SQL in the question.

Here's an alternative that would give the additional 2016 row:

 SELECT i.region,
        DATEPART(YEAR, i.d1) AS YearNumber,
        SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
 FROM @info i
 CROSS APPLY (VALUES(CASE WHEN DATEPART(YEAR, d1) = '2015' THEN ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) ELSE 0 END)) AS rd(Calc)
 GROUP BY Region, DATEPART(YEAR, i.d1);