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  lt  gt  0 AND a SecurityW IS NOT NULL AND a TransferW lt  gt  0 AND a TransferWIS NOT NULL AND a StaffW lt  gt  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

User · Answer

this should work  haven t tried though  this will exclude zero  NULL is excluded by default  AVG  CASE WHEN SecurityW  lt  gt  0 THEN SecurityW ELSE NULL END

User · Answer

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  gt    20130401         AND a ActualTime  lt   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

User · Answer

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  lt  gt  0 AND a SecurityW IS NOT NULL AND a TransferW lt  gt  0 AND a TransferWIS IS NOT NULL AND a StaffW lt  gt  0 AND a StaffWIS IS NOT NULL AND MONTH a ActualTime    4 AND YEAR a ActualTime    2013

User · Answer

worked for me   AVG CASE WHEN SecurityW  lt  gt  0 THEN SecurityW ELSE NULL END

User · Answer

In Case of not considering  0  or  NULL  in average function  Simply use   AVG NULLIF your column name 0

[sql] Get AVG ignoring Null or Zero values

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008-r2