Add calculated column with where clause

Trying to add a calculated column where another column = 0, however, I'm not sure how to get around the error

An aggregate may not appear in the set list

along with

Window functions can only appear in...

I'm essentially only trying to generate a weighted value for records where Source_Value is not 0.

What I have:

CREATE TABLE #CURRENT_INPUT (
    ShipYear INT,
    Group_Name VARCHAR(20),
    Total_Count INT,
    Source_Value INT,
);

INSERT INTO #CURRENT_INPUT(ShipYear,Group_Name,Total_Count,Source_Value)
VALUES
      ('2011','TestA','5','3'),
      ('2011','TestA','5','1'),
      ('2011','TestA','5','2'),
      ('2011','TestA','5','2'),
      ('2012','TestC','5','2'),
      ('2012','TestC','5','1'),
      ('2012','TestC','5','0'),
      ('2012','TestC','5','7')


ALTER TABLE #CURRENT_INPUT
    ADD Calc_Values varchar(55)

UPDATE #CURRENT_INPUT
SET Calc_Values = CASE
                      WHEN Source_Value <> 0 
                          THEN ISNULL(SUM(total_count) / NULLIF(SUM(SUM(total_count)) OVER (PARTITION BY ShipYear, group_name), 0), 0) 
                          ELSE Source_Value 
                  END;

Desired output (keeping it simple) table would like this:

ShipYear	Group_Name	Total_Count	Calc_Values
2011	TestA	5	0.25
2011	TestA	5	0.25
2011	TestA	5	0.25
2011	TestA	5	0.25
2012	TestC	5	0.33
2012	TestC	5	0.33
2012	TestC	5	0.0
2012	TestC	5	0.33

My only other thought is to create a copy of the main table with source_value=0 records excluded and then join this calculated column back to the main table. I was hoping for a cleaner approach, as the main table is quite large with an extensive number of columns.

Any suggestions would be greatly appreciated.

Solution

Few issues with your attempt:

You were getting over enthusiastic with your SUMs - take a layer of SUMs out (I also removed null checks because they don't normally affects sums but you might need to add them back).
You need your conditional CASE expression to be inside the SUM not outside it.
Also need to force the values to decimal to avoid integer division.

CASE
    WHEN Source_Value <> 0 
    THEN CONVERT(decimal(9,2),Total_Count*1.0 / SUM(CASE WHEN Source_Value > 0 THEN Total_Count ELSE 0 END*1.0) OVER (PARTITION BY ShipYear, Group_Name))
    ELSE Source_Value
END

Returns for the data provided:

ShipYear	Group_Name	Total_Count	Source_Value	Calc_Value
2011	TestA	5	3	0.25
2011	TestA	5	1	0.25
2011	TestA	5	2	0.25
2011	TestA	5	2	0.25
2012	TestC	5	2	0.33
2012	TestC	5	1	0.33
2012	TestC	5	0	0.00
2012	TestC	5	7	0.33

And this can then be simply converted into an UPDATE one way is using a CTE e.g.

WITH cte AS (
    SELECT *
        , CASE
            WHEN Source_Value <> 0 
            THEN CONVERT(decimal(9,2),Total_Count*1.0 / SUM(CASE WHEN Source_Value > 0 THEN Total_Count ELSE 0 END*1.0) OVER (PARTITION BY ShipYear, Group_Name))
            ELSE Source_Value
        END NewCalc_Value
    FROM #CURRENT_INPUT
)
UPDATE cte SET Calc_Value = NewCalc_Value;

DBFiddle