SQL Server AVG function to return NULL if all values ​​are NULL

Question

I have a table like this

+--+-----+----+ |ID|Entry|Exit| +--+-----+----+ |18|32154|NULL| +--+-----+----+ |19|NULL |NULL| +--+-----+----+ 

When I select AVG (Entry), it correctly gives me 32154, when I select AVG (Exit), it explodes, saying: "The data type of the operand type is not valid for the avg operator."

How can I get NULL as an average for a column that has only NULL values?

Thanks,

Answer 1

The problem is that the Output column does not have a data type that is compatible with the SUM function.

You can run this query to see that you really get NULL from SUM if all the values ​​are NULL (and the correct data type)

 select sum(a) from (select convert(int, null) a union select null) a 

Answer 2

Try using CASE as follows

 SELECT CASE WHEN SUM(Exit) IS NULL THEN NULL ELSE AVG(Exit) END AS MyAverage FROM MyTable 

I think the problem is with the column name. Just change the column name to ExitCol and check.

In this case, even SELECT AVG(ExitCol) AS MyAverage FROM MyTable will also work

Answer 3

use set xact_abort off to return null

  declare @a table (id int, amount numeric(10,2)) insert into @a values (1, null), (2,null), (3,null) set xact_abort off select AVG(amount) from @a 

Answer 4

 Select avg(isnull(Exit,0)) from table