TSQL - Average of all values in a column that are not zero
21,352
Solution 1
SELECT
AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END)
....
AVG won't take into account NULL values. Or this
AVG (NULLIF(Value, 0))
Solution 2
, ( SELECT AVG(a) FROM
(
SELECT NULLIF([Column1], 0)
UNION ALL
SELECT NULLIF([Column2], 0)
UNION ALL
SELECT NULLIF([Column3], 0)
UNION ALL
SELECT NULLIF([Column4], 0)
) T (a)
) AS [4 Column Average]
Comments
-
Justin808 over 4 years
I'm in the process of writing a report and am looking to get the average value of an age column. The problem is that not all rows have an age.
If the values for the column are 0 2 4 I would want 3 returned, not 2. I can not simply exclude the zero rows with a WHERE as I'm using using other columns in those rows. Is there such a thing as a AvgIfNotZero type of function?