Obtener AVG ignorando valores Nulos o Cero

¿Cómo puedo obtener el AVG de una columna ignorando los valores NULL y cero?

Tengo tres columnas para obtener su promedio, trato de usar el siguiente 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

¡No obtengo ningún resultado, sin embargo las tres columnas tienen valores que incluyen NULL y ceros!

¿Hay alguna forma de excluir los valores nulos antes de obtener la media?

ejemplo: AVERAGE(NOTNULL(SecurityW))

Darren
Pregunta editada 2º julio 2013 в 12:03
Programación
sql sql-server tsql sql-server-2008-r2
2º julio 2013 в 11:57
1 vista

NULLya se ignora, así que puedes usarNULLIFpara convertir0enNULL. Además no necesitasDISTINCTy tuWHEREenActualTime` no es 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 No tengo ni idea de lo que Table2 b está en la consulta original para que no hay ninguna condición de unión para que he omitido de mi respuesta.

Comentarios (4)

esto debería funcionar, aunque no lo he probado. esto excluirá cero. NULL se excluye por defecto

AVG (CASE WHEN SecurityW  0 THEN SecurityW ELSE NULL END)
Comentarios (0)

Ya se intenta filtrar los valores NULL con NOT NULL. He cambiado esto por IS NOT NULL en la cláusula WHERE para que se ejecute. Podemos refactorizar esto eliminando la función ISNULL del método AVG. Además, dudo que realmente necesites bigint así que podemos eliminar el cast.

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
Comentarios (0)