How to Calculate Non Zero Rows Count, Average IF when Sum of the Rows is greater than Zero in SQL Server

How to Calculate Non Zero Rows Count and Average IF when Sum of the Rows 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].[ResourceCostSample]

Now from the above Table, we need to Calculate the Average Cost details for each Month by Grouping data by [RoleIdentifier], [CostYear].
While calculating Average Cost for Month(eg. M1), per each [RoleIdentifier] by [CostYear],
we need to consider the Count of Rows only where the M1>0, means we have to Ignore the Zeros along with Null values.
We know that, the COUNT and SUM Functions will ignore NULL values in SQL SERVER.
But COUNT Function will consider Zero values, which we should not consider here.
Now we will see the COUNT and SUM of values for each Month by grouping as discussed.

SELECT [RoleIdentifier], [CostYear], 
SUM(M1) M1Sum,
SUM(Case When M1>0 Then 1 Else M1 End) M1Cnt,
SUM(M2) M2Sum,
SUM(Case When M2>0 Then 1 Else M2 End) M2Cnt,
SUM(M3) M3Sum,
SUM(Case When M3>0 Then 1 Else M3 End) M3Cnt,
SUM(M4) M4Sum,
SUM(Case When M4>0 Then 1 Else M4 End) M4Cnt,
SUM(M5) M5Sum,
SUM(Case When M5>0 Then 1 Else M5 End) M5Cnt,
SUM(M6) M6Sum,
SUM(Case When M6>0 Then 1 Else M6 End) M6Cnt,
SUM(M7) M7Sum,
SUM(Case When M7>0 Then 1 Else M7 End) M7Cnt,
SUM(M8) M8Sum,
SUM(Case When M8>0 Then 1 Else M8 End) M8Cnt,
SUM(M9) M9Sum,
SUM(Case When M9>0 Then 1 Else M9 End) M9Cnt,
SUM(M10) M10Sum,
SUM(Case When M10>0 Then 1 Else M10 End) M10Cnt,
SUM(M11) M11Sum,
SUM(Case When M11>0 Then 1 Else M11 End) M11Cnt,
SUM(M12) M12Sum,
SUM(Case When M12>0 Then 1 Else M12 End) M12Cnt
From [dbo].[ResourceCostSample]
Group By [RoleIdentifier], [CostYear]
GO

Result :

Now we will calculate the Average as discussed above. We will create a View here to show the Average Costing details, instead of updating the Source table.
CREATE VIEW vw_AvgCostingDetails
AS
(
SELECT  [Id], RC.[RoleIdentifier], [RoleID],[RoleName],[Function]
        ,[CostCenterCode],[CostGeo], RC.[CostYear]
        ,(CASE When T1.[M1Sum]>0 Then T1.[M1Sum]/T1.[M1Cnt] Else 0 End) AS [M1]
        ,(CASE When T1.[M2Sum]>0 Then T1.[M2Sum]/T1.[M2Cnt] Else 0 End) AS [M2]
,(CASE When T1.[M3Sum]>0 Then T1.[M3Sum]/T1.[M3Cnt] Else 0 End) AS [M3]
,(CASE When T1.[M4Sum]>0 Then T1.[M4Sum]/T1.[M4Cnt] Else 0 End) AS [M4]
,(CASE When T1.[M5Sum]>0 Then T1.[M5Sum]/T1.[M5Cnt] Else 0 End) AS [M5]
,(CASE When T1.[M6Sum]>0 Then T1.[M6Sum]/T1.[M6Cnt] Else 0 End) AS [M6]
,(CASE When T1.[M7Sum]>0 Then T1.[M7Sum]/T1.[M7Cnt] Else 0 End) AS [M7]
,(CASE When T1.[M8Sum]>0 Then T1.[M8Sum]/T1.[M8Cnt] Else 0 End) AS [M8]
,(CASE When T1.[M9Sum]>0 Then T1.[M9Sum]/T1.[M9Cnt] Else 0 End) AS [M9]
,(CASE When T1.[M10Sum]>0 Then T1.[M10Sum]/T1.[M10Cnt] Else 0 End) AS [M10]
,(CASE When T1.[M11Sum]>0 Then T1.[M11Sum]/T1.[M11Cnt] Else 0 End) AS [M11]
,(CASE When T1.[M12Sum]>0 Then T1.[M12Sum]/T1.[M12Cnt] Else 0 End) AS [M12]
FROM [dbo].[ResourceCostSample] RC
INNER JOIN
( SELECT [RoleIdentifier], CostYear, 
SUM(M1) M1Sum,
SUM(Case When M1>0 Then 1 Else M1 End) M1Cnt,
SUM(M2) M2Sum,
SUM(Case When M2>0 Then 1 Else M2 End) M2Cnt,
SUM(M3) M3Sum,
SUM(Case When M3>0 Then 1 Else M3 End) M3Cnt,
SUM(M4) M4Sum,
SUM(Case When M4>0 Then 1 Else M4 End) M4Cnt,
SUM(M5) M5Sum,
SUM(Case When M5>0 Then 1 Else M5 End) M5Cnt,
SUM(M6) M6Sum,
SUM(Case When M6>0 Then 1 Else M6 End) M6Cnt,
SUM(M7) M7Sum,
SUM(Case When M7>0 Then 1 Else M7 End) M7Cnt,
SUM(M8) M8Sum,
SUM(Case When M8>0 Then 1 Else M8 End) M8Cnt,
SUM(M9) M9Sum,
SUM(Case When M9>0 Then 1 Else M9 End) M9Cnt,
SUM(M10) M10Sum,
SUM(Case When M10>0 Then 1 Else M10 End) M10Cnt,
Sum(M11) M11Sum,
SUM(Case When M11>0 Then 1 Else M11 End) M11Cnt,
SUM(M12) M12Sum,
SUM(Case When M12>0 Then 1 Else M12 End) M12Cnt
From [dbo].[ResourceCostSample]
Group By [RoleIdentifier], [CostYear]
) T1
ON RC.[RoleIdentifier]=T1.[RoleIdentifier] AND
RC.[CostYear]=T1.[CostYear]
)
GO

Select * From vw_AvgCostingDetails
GO

Result :

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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