How to Calculate Columns Count IF when Sum of the Columns is greater than Zero in SQL Server

How to Calculate Columns Count, Average IF when Sum of the Columns is greater than Zero in SQL Server
Scenario:
Suppose we have a Sample table as below showing details of the Cost by Month associated with the Roles, Cost split by CostCenter, CostGeo and CostYear.
Select * From [dbo].[ResourceCostingSample]

Now in the above Table, we have to Calculate the field "AvgCost By RoleGeoYear" as per below :
First we need to find the Sum of the Costs of all Months, Sum(M1, M2,...M11, M12) by Grouping the data by [RoleIdentifier], [CostGeo], [CostYear].
Similarly we need to find the Count of the Months, where SUM(Cost)>0, which means we have to consider only where SUM(M1)>0, SUM(M2)>0...SUM(M11)>0, SUM(M12)>0.
Eg:
the Count of Months is 4 for Role77AnalytstHR ; US ; 2014
the Count of Months is 8 for Role77AnalytstHR ; US ; 2015

We can achieve the SUM, and COUNT as expected above using the below SQL Query :
SELECT [RoleIdentifier], [CostGeo], [CostYear],
SUM(ISNULL(M1,0)+ISNULL(M2,0)+ISNULL(M3,0)+ISNULL(M4,0)+ISNULL(M5,0)+
ISNULL(M6,0)+ISNULL(M7,0)+ISNULL(M8,0)+
ISNULL(M9,0)+ISNULL(M10,0)+
ISNULL(M11,0)+ISNULL(M12,0)) AS SumOfCostMonths,
( CASE WHEN SUM(M1)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M2)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M3)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M4)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M5)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M6)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M7)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M8)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M9)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M10)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M11)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M12)>0 THEN 1 ELSE 0 END )  AS CountOfCostMonths
From [dbo].[ResourceCostingSample]
Group By [RoleIdentifier], [CostGeo], [CostYear]

Result:

Next we need to Calculate and Update the  [AvgCost By RoleGeoYear] in the main Sample table by mapping it to the above summary table.
[AvgCost By RoleGeoYear] = [SumOfCostMonths]/[CountOfCostMonths]
We can use the below SubQuery method to perform the final update.

UPDATE [dbo].[ResourceCostingSample]
SET [AvgCost By RoleGeoYear] = S1.[SumOfCostMonths]/S1.[CountOfCostMonths]
From [dbo].[ResourceCostingSample] RC 
Inner Join 
(SELECT [RoleIdentifier], [CostGeo], [CostYear],
SUM(ISNULL(M1,0)+ISNULL(M2,0)+ISNULL(M3,0)+ISNULL(M4,0)+ISNULL(M5,0)+
ISNULL(M6,0)+ ISNULL(M7,0)+ISNULL(M8,0)+
ISNULL(M9,0)+ISNULL(M10,0)+
         ISNULL(M11,0)+ISNULL(M12,0)) AS SumOfCostMonths,
( CASE WHEN SUM(M1)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M2)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M3)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M4)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M5)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M6)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M7)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M8)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M9)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M10)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M11)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M12)>0 THEN 1 ELSE 0 END ) AS CountOfCostMonths
From [dbo].[ResourceCostingSample]
Group By [RoleIdentifier], [CostGeo], [CostYear]
) S1
  ON RC.[RoleIdentifier]=S1.[RoleIdentifier] AND
RC.[CostGeo]=S1.[CostGeo] AND
RC.[CostYear]=S1.[CostYear]

Final Result :
Select * From [dbo].[ResourceCostingSample]

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------