TSQL-列中所有非零值的平均值 [英] 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.

如果该列的值为0 2 4，我希望返回3，而不是2.我不能简单地使用WHERE排除零行，因为我正在使用这些行中的其他列.是否存在诸如AvgIfNotZero类型的函数?

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?

推荐答案

SELECT

    AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END)
    ....

AVG不会考虑NULL值.还是这个

AVG won't take into account NULL values. Or this

    AVG (NULLIF(Value, 0))

这篇关于TSQL-列中所有非零值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！