# How to calculate the Average of fields that are not zero?

• ### Question

• I have a few columns: Week1, Week2, Week3, Week4, Week5

i.e.

Week1 = 5

Week2 = 0

Week3 = 10

Week4 = 7

Week5 = 0

How do I calculate the average based on the fields that are not zero?

Incorrect:
(5+0+10+7+0)/5 = 4.4

Correct:
(5+0+10+7+0)/3 = 7.3

How to let create a custom column to let the SQL know that I only want to divide by 3 instead of 5?

Wednesday, May 27, 2015 11:39 PM

• Hi dchencm,

Based on my test, please refer to the following query to achieve your requirement:

SELECT [EmployeeID]
,[Last Name]
,([1WksAgo]+[2WksAgo]+[3WksAgo]+[4WksAgo]+[5WksAgo])/(
CASE WHEN [1WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [2WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [3WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [4WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [5WksAgo]=0 THEN 0 ELSE 1 END ) as [AVG HRs]
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]

FROM [CMHR].[dbo].[tbl52weekHours_20150527]
WHERE [Last Name] = 'RAZA'
GROUP BY [EmployeeID]
,[Last Name]
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]
Thanks,
Katherine Xiong

Katherine Xiong
TechNet Community Support

Friday, May 29, 2015 2:05 AM

### All replies

Dim i As Integer = 1
For Each week As String In TextBox1.text
If week > 0 Then
i = i + 1
End If
Next

Wednesday, May 27, 2015 11:55 PM
• I assume you want SQL, posting here. Something like

SELECT AVG(CASE WHEN col = 0 THEN NULL ELSE col END)

FROM ...

You replace 0 with NULL and NULL are not included in aggregate functions.

Tibor Karaszi, SQL Server MVP | web | blog

Thursday, May 28, 2015 6:51 AM

Dim i As Integer = 1
For Each week As String In TextBox1.text
If week > 0 Then
i = i + 1
End If
Next

how do i achieve this in SQL?
Thursday, May 28, 2015 10:12 PM
• I posted SQL code in my reply. Did you try that? If you want full-blown ready-to-execute code, then please share DDL we can use-

Tibor Karaszi, SQL Server MVP | web | blog

Thursday, May 28, 2015 10:13 PM
• I assume you want SQL, posting here. Something like

SELECT AVG(CASE WHEN col = 0 THEN NULL ELSE col END)

FROM ...

You replace 0 with NULL and NULL are not included in aggregate functions.

Tibor Karaszi, SQL Server MVP | web | blog

Then I still need to count how many non-zero columns so i can divide the total by the # of non-zeros.
so how do i do that?
Thursday, May 28, 2015 10:14 PM
• I posted SQL code in my reply. Did you try that? If you want full-blown ready-to-execute code, then please share DDL we can use-

Tibor Karaszi, SQL Server MVP | web | blog

SELECT [EmployeeID]
,[Last Name]
,AVG(CASE
WHEN (   [1WksAgo] = 0)
OR ([2WksAgo] = 0)
OR ([3WksAgo] = 0)
OR ([4WksAgo] = 0)
OR ([5WksAgo] = 0)
THEN NULL
ELSE ( [1WksAgo]
+ [2WksAgo]
+ [3WksAgo]
+ [4WksAgo]
+ [5WksAgo]
)
END) as '--- AVG HRs ---'
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]

FROM [CMHR].[dbo].[tbl52weekHours_20150527]
WHERE [Last Name] = 'RAZA'
GROUP BY [EmployeeID]
,[Last Name]
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]

Thursday, May 28, 2015 10:35 PM
• Hi dchencm,

Based on my test, please refer to the following query to achieve your requirement:

SELECT [EmployeeID]
,[Last Name]
,([1WksAgo]+[2WksAgo]+[3WksAgo]+[4WksAgo]+[5WksAgo])/(
CASE WHEN [1WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [2WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [3WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [4WksAgo]=0 THEN 0 ELSE 1 END +
CASE WHEN [5WksAgo]=0 THEN 0 ELSE 1 END ) as [AVG HRs]
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]

FROM [CMHR].[dbo].[tbl52weekHours_20150527]
WHERE [Last Name] = 'RAZA'
GROUP BY [EmployeeID]
,[Last Name]
,[1WksAgo]
,[2WksAgo]
,[3WksAgo]
,[4WksAgo]
,[5WksAgo]
Thanks,
Katherine Xiong

Katherine Xiong
TechNet Community Support

Friday, May 29, 2015 2:05 AM