locked
How to calculate the Average of fields that are not zero? RRS feed

  • 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

Answers

  • 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

  • how about a loop

    Dim i As Integer = 1
            For Each week As String In TextBox1.text
                If week > 0 Then
                    i = i + 1
                End If
    'add weeks divide by i
            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
  • how about a loop

    Dim i As Integer = 1
            For Each week As String In TextBox1.text
                If week > 0 Then
                    i = i + 1
                End If
    'add weeks divide by i
            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