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

Question

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?

score 47 · Accepted Answer · answered Jan 20 '11 at 19:46

47

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

answered Jan 20 '11 at 19:46

gbn

422,506
82
585
676

score 1 · Answer 2 · answered Mar 08 '18 at 12:30

1

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

answered Mar 08 '18 at 12:30

Milo Woodward

11
1

You should give more context to an answer rather than just dropping a block of code that the OP or future readers may not understand. – zgue Mar 08 '18 at 13:01

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

2 Answers2

Linked

Related