Check for NULL values while computing the SUM of a dataset column.

Question

1.00/5 (1 vote)

See more:

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.

Posted 17-Dec-14 16:50pm

aakar

Add a Solution

2 solutions

Solution 2

In addition to solution 1...

I just realized that using above queries/statements together with Average 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:

C#

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:

C#

total = dt.AsEnumerable().Where(x=>x["ABC"]!=DBNull.Value).Average(x=>(double)x["ABC"]);

you'll get 18 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

Posted 18-Dec-14 0:26am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-12-17T16:52:00

Solution 1

C#

decimal total = ds.Tables[0].AsEnumerable()
 .Sum(r => r.Field<decimal?>("ABC") ?? 0);

or

C#

decimal? total = ds.Tables[0].AsEnumerable()
 .Sum(r => r.Field<decimal?>("ABC"));

without Field extension method

C#

var total = ds.Tables[0].Compute("Sum(ABC)", "");

or

C#

var totalABC = ds.Tables[0].AsEnumerable().Sum(x => x.IsNull("ABC")?0.0m:Convert.ToDecimal(x["ABC"]));

Posted 17-Dec-14 16:52pm

DamithSL

Updated 17-Dec-14 21:39pm

v3

Comments

aakar 18-Dec-14 3:25am

I tried the two solutions with the field extension method, but I am getting a "Specified Cast is not Valid" error message.

DamithSL 18-Dec-14 3:26am

what is the database column type?

aakar 18-Dec-14 3:42am

The DB datatype is float

DamithSL 18-Dec-14 3:46am

then you need to change decimal to double
you should have told these information when you ask the question. anyway what is your database ?

Maciej Los 18-Dec-14 5:05am

+5!

DamithSL 18-Dec-14 5:25am

thank you, Maciej

aakar 18-Dec-14 6:56am

Thanks Damith! the solution works fine for the columns from the DB.
I am using SQL Server as the DB. sorry for not having mentioned it in my post earlier!
However, if I use the above for a column that I am adding to my Dataset at runtime, it still tells me "Specified Cast is not Valid".

DamithSL 18-Dec-14 9:14am

It is depending on the column data type.