在计算数据集列的SUM时检查NULL值。 [英] Check for NULL values while computing the SUM of a dataset column.
问题描述
我正在尝试计算数据集中特定列的总和。
我正在使用以下代码来实现它。
var totalABC = ds.Tables [0] .AsEnumerable()。SUM(x => Convert.ToDecimal(x [ABC]));
其中ABC是我总结的列。
但是,在运行时我收到错误说:
无法将System.DBNull转换为十进制类型。如何在上面的代码中检查Null并仅将非Null值转换为十进制?
提前致谢,
Aakar。
decimal 总计= ds.Tables [ 0 ]。AsEnumerable()
.Sum(r = > r.Field< decimal?> ( ABC )?? 0 );
或
十进制? total = ds.Tables [ 0 ]。AsEnumerable()
.Sum(r = > r.Field< decimal?> ( ABC ));
没有字段扩展方法
var total = ds.Tables [ 0 ]。计算( Sum(ABC), );
或
var totalABC = ds .Tables [ 0 ]。AsEnumerable()。Sum(x = > x.IsNull( ABC)? 0 .0m:转换.ToDecimal(x [ ABC]));
除了解决方案1 ...
我刚刚意识到将上述查询/语句与<$ c一起使用$ c>平均函数可能是错误结果的原因。为什么?
假设存储在数据表中的数据是:{5,DBNull.Value,42,7,DBNull.Value}
执行命令时:
total = dt.AsEnumerable()。Average(x => x [ ABC] == DBNull.Value? 0 :( double )x [ ABC]);
你会得到:10.8
因此。
但是当你执行如下命令时:
total = dt.AsEnumerable()。其中(x => x [ ABC]!= DBNull.Value)。平均值(x = > ( double )x [ ABC]);
你将得到18
作为结果。
解释很简单:总和相同(54),但项目数不同。
第一种情况,null
值替换为0(零),因此项目数等于5。54/5 = 10.8
在第二种情况下,null
值被忽略,因此物品数量等于三。54/3 = 18
Hi,
I am trying to compute the SUM total of a particular column in my DataSet.
I am using the below code to achieve it.
var totalABC = ds.Tables[0].AsEnumerable().SUM(x => Convert.ToDecimal(x["ABC"]));
where ABC is the column that I am summing up.
However, at runtime I get an error saying :
Cannot convert System.DBNull to decimal type. How do I check for Nulls in the above code & only convert the non-Null values to decimal?
Thanks in advance,
Aakar.
decimal total = ds.Tables[0].AsEnumerable() .Sum(r => r.Field<decimal?>("ABC") ?? 0);
or
decimal? total = ds.Tables[0].AsEnumerable() .Sum(r => r.Field<decimal?>("ABC"));
without Field extension method
var total = ds.Tables[0].Compute("Sum(ABC)", "");
or
var totalABC = ds.Tables[0].AsEnumerable().Sum(x => x.IsNull("ABC")?0.0m:Convert.ToDecimal(x["ABC"]));
In addition to solution 1...
I just realized that using above queries/statements together withAverage
function could be the reason of false results. Why?
Let's say, the data stored in the datatable are:{5, DBNull.Value, 42, 7, DBNull.Value}
When you execute command:
total = dt.AsEnumerable().Average(x=>x["ABC"]==DBNull.Value ? 0 : (double)x["ABC"]);
you'll get:10.8
as a result.
But when you execute command like:
total = dt.AsEnumerable().Where(x=>x["ABC"]!=DBNull.Value).Average(x=>(double)x["ABC"]);
you'll get18
as result.
The explanation is quite simple: the sum is the same (54), but the count of items is differ.
In first case,null
values are replaced with 0 (zeros), so the count of items is equal five.54/5=10.8
In second case,null
values are ignored, so the count of items is equal three.54/3=18
这篇关于在计算数据集列的SUM时检查NULL值。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!