Получить AVG, игнорируя значения Null или Zero

Как я могу получить AVG столбца, игнорирующего NULL и нулевые значения?

У меня есть три столбца, чтобы получить их среднее значение, я пытаюсь использовать следующий 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 и нули!

В любом случае, чтобы исключить нулевые значения до получения среднего значения?

Пример: AVERAGE(NOTNULL(SecurityW))

Ответы

Ответ 1

NULL уже игнорируется, поэтому вы можете использовать NULLIF, чтобы превратить 0 в NULL. Также вам не нужно DISTINCT, а ваш WHERE на ActualTime не поддается.

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 находится в исходном запросе, так как для него нет условия соединения, поэтому он опустил его из моего ответа.

Ответ 2

это должно работать, но не пробовал. это исключает ноль. По умолчанию исключается NULL.

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

Ответ 3

работал у меня:

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

Ответ 4

Вы уже пытаетесь отфильтровать значения NULL с помощью NOT NULL. Я изменил это на IS NOT NULL в предложении WHERE, чтобы он выполнялся. Мы можем реорганизовать это, удалив функцию ISNULL в методе AVG. Кроме того, я сомневаюсь, что вам действительно понадобится bigint, чтобы мы могли удалить бросок.

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

Ответ 5

В случае отсутствия "0" или "NULL" в средней функции. Просто используйте

AVG(NULLIF(your_column_name,0))