Get AVG ignoring Null or Zero values

Question

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))

Baggage

All those ANDs mean that you'll only get rows which have non-NULL values in all 3 columns. Also, your NOT NULL checks aren't required, since Column<>0 evaluates as UNKNOWN if Column is NULL. – Dynamometer

Answer 1

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.

Briareus

btw I don't think you achve anything by converting an integer to bigint – Prenatal

@Prenatal - It can prevent an arithmetic overflow error if the SUM is too large. SELECT AVG(C) FROM (SELECT 2147483647 UNION ALL SELECT 2147483647) T(C) fails but SELECT AVG(CAST(C AS BIGINT)) FROM (SELECT 2147483647 UNION ALL SELECT 2147483647) T(C) succeeds. – Briareus

@DanielGimenez your solution currently doesn't ignore 0 correctly. This answer does using nullif. – Prenatal

@DanielGimenez - No it won't. Try it. SELECT AVG(NULLIF(C,0)) FROM (SELECT 30 UNION ALL SELECT null UNION ALL SELECT 0) T(C) returns 30 – Briareus

Answer 2

this should work, haven't tried though. this will exclude zero. NULL is excluded by default

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

Whatnot

Answer 3

In Case of not considering '0' or 'NULL' in average function. Simply use

AVG(NULLIF(your_column_name,0))

Fasciate

Answer 4

worked for me：

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

Simile

Answer 5

You already attempt to filter out NULL values with NOT NULL. I have changed this to IS NOT NULL in the WHERE clause so it will execute. We can refactor this by removing the ISNULL function in the AVG method. Also, I doubt you'll actually need bigint so we can remove the 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

Polytechnic

Answer 6

Null values are anyways excluded from the avg calculation. If you need to exclude zero values as well, you could simply use the decode function inside your avg function:-

SELECT AVG(decode(a.SecurityW,0,null,a.SecurityW)) as Average1
 ,AVG(decode(a.TransferW,0,null,a.TransferW)) as Average2
 ,AVG(decode(a.StaffW,0,null,a.StaffW)) as Average3 
FROM Table1 a,  Table2 b
WHERE MONTH(a.ActualTime) = 4
AND YEAR(a.ActualTime) = 2013;

Scraperboard

Note: DECODE is Oracle. See this for Sql Server equivalence #1559741 – Lecher