CODEWITHSUNDEEP
t-sqlaverage
Justin808
Justin808

Reputation: 21512

TSQL - Average of all values in a column that are not zero

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?

Upvotes: 21

Views: 23182

Answers (2)

Milo Woodward
Milo Woodward

Reputation: 11

, ( 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]

Upvotes: 1

gbn
gbn

Reputation: 432271

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))

Upvotes: 47

Related Questions