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
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?
example: AVERAGE(NOTNULL(SecurityW))
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 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.