如果所有值都为NULL，则SQL Server AVG函数返回NULL

I have a table similar to 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 blows up saying "Operand data type void type is invalid for avg operator."

当我选择AVG（Entry）时它正确地给了我32154，当我选择AVG（退出）时它会爆炸说“操作数据类型无效类型对于平均操作符无效”。

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

如何将NULL作为仅具有NULL值的列的平均值？

Thanks,

谢谢，

4 个解决方案

#1

The problem is that the Exit column doesn't have a data type that is compatible with the SUM function.

问题是Exit列没有与SUM函数兼容的数据类型。

You can run this query to see that you indeed get NULL from SUM if all values are NULL (and a proper data type)

如果所有值都为NULL（并且是正确的数据类型），您可以运行此查询以查看确实从SUM中获取NULL

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

#2

Try using CASE like this

尝试使用像这样的CASE

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.

我认为问题在于列名。只需将列名更改为ExitCol并检查即可。

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

在这种情况下，即使SELECT AVG（ExitCol）AS MyAverage FROM MyTable也可以工作

#3

use set xact_abort off to get it to return null

使用set xact_abort off使其返回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

#4

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

#1