获取忽略 Null 或零值的 AVG

2022-01-16 00:00:00 sql tsql sql-server sql-server-2008-r2

如何获得忽略 NULL 和零值的列的 AVG?

How can I get the AVG of a column ignoring NULL and zero values?

我有三列来获取它们的平均值，我尝试使用以下脚本:

I have three columns to get their average, I try to use the following script:

SELECT distinct
     AVG(cast(ISNULL(a.SecurityW,0) as bigint)) as Average1
     ,AVG(cast(ISNULL(a.TransferW,0) as bigint)) as Average2
     ,AVG(cast(ISNULL(a.StaffW,0) as bigint)) as Average3
FROM Table1 a,  Table2 b
WHERE a.SecurityW <> 0 AND a.SecurityW IS NOT NULL
AND a.TransferW<> 0 AND a.TransferWIS NOT NULL
AND a.StaffW<> 0 AND a.StaffWIS NOT NULL
AND MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013

我没有得到任何结果，但是这三列的值包括 NULL 和零！

I don't get any results, however the three columns have values including NULL and zeros!

在获取平均值之前是否有排除空值的方法?

Is there anyway to exclude null values before getting the average?

示例:AVERAGE(NOTNULL(SecurityW))

推荐答案

NULL 已经被忽略所以你可以使用 NULLIF 将 0为 NULL.您也不需要 DISTINCT 并且您在 ActualTime 上的 WHERE 是不可搜索的.

NULL is already ignored so you can use NULLIF to turn 0 to NULL. Also you don't need DISTINCT and your WHERE on ActualTime is not sargable.

SELECT AVG(cast(NULLIF(a.SecurityW, 0) AS BIGINT)) AS Average1,
       AVG(cast(NULLIF(a.TransferW, 0) AS BIGINT)) AS Average2,
       AVG(cast(NULLIF(a.StaffW, 0) AS BIGINT))    AS Average3
FROM   Table1 a
WHERE  a.ActualTime >= '20130401'
       AND a.ActualTime < '20130501'

PS 我不知道原始查询中的 Table2 b 是什么，因为它没有连接条件，所以在我的回答中省略了它.

PS I have no idea what Table2 b is in the original query for as there is no join condition for it so have omitted it from my answer.

相关文章