¿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))
NULLya se ignora, así que puedes usar
NULLIFpara convertir
0en
NULL. Además no necesitas
DISTINCTy tu
WHEREen
ActualTime` 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.
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