Today I was trying to find the average value of a DataTable.  Seems simple, except the values coming back were always integers.  What was going on?  Read on for a sneaky tidbit which could be mucking up YOUR data!

Today I ran SMACK into a very interesting problem.  Let me set it up for you. 

I have a DataTable with a bunch of rows'n'columns and I want to add a TOTALS row.  Sound  familiar so far right?  Next I want to "compute" the average values of one of the columns.  Sounds cool, easy enough, especially when there's the DataTable.Compute() method for me!  

COOL!  UNTIL!!!!!!!!!!!  you realize the nasty side of the Compute() method is it returns the SAME datatype as the column.  YES!  That's right, the very same datatype as the column.  Maybe YOU expected that, but I certainly didn't.  Why is that?  Compute returns back an object and I just figured if I'm doing division, it'll return me back something with the decimal which I can then cast to my own int or double, or decimal or whatever I want.  But nope, the decision's made for me, before I can cast it.

Ok, back to my challenge at hand.  Our client wants all their averages to use the MidpointRounding.AwayFromZero (please read this for further info), and to do THAT math on our averages column, I want to compute the AVG on the column (I expect the value coming back to be a double since I'm dividing right?) then I can cast/round/do what-ever I want with it.  Nope, sorry!  It's already cast for me when it returns BACK from the compute method.

Huh?  WTF?  Want more info?  Sure. 

I wrote a small test app for this, and at runtime, I put a breakpoint and checked out two of the Computed columns.  See how the average[NumberOfPlayers] is an int and the average[NumberOfPlayersDouble] is a double Type?  There's no casting done there, those are the native/default returning datatypes being returned via the Compute calculations.

The code to test above looks like this. 

DataRow average = table.NewRow();
average["TeamName"] = "Avg # Players";
average["NumberOfPlayers"] = table.Compute( "AVG(NumberOfPlayers)", "" );
average["NumberOfPlayersDouble"] = table.Compute( "AVG(NumberOfPlayersDouble)", "" );
average["NumberOfPlayersRounded"] =
 Math.Round(
  Convert.ToDouble(
   table.Compute( "AVG(NumberOfPlayersDouble)", "" )
  ), 0, MidpointRounding.AwayFromZero );
table.Rows.Add( average );

Now for the next issue.  When you set the value of average[NumberOfPlayersDouble] column, you're getting back an object which LOOKS like a double.  What's the big deal... the code will automatically truncate your double value to an int, ya, on your behalf!  DOH!  So, IF you want it to STAY a double, you'll have to explicitly cast it.  Here's the udpated code.

average["NumberOfPlayersDouble"] = Convert.ToDouble( table.Compute( "AVG(NumberOfPlayersDouble)", "" ) );

Now, when you run the code, you'll get something you expected, decimal points for your average calculation.

The last point I want to make is our client wanted the average values rounded using the MidpointRounding.AwayFromZero enumeration.  This bascially means, every ".5" value gets upped to the next integer.  The main thing to note here is you CANNOT just truncate the average value or cast it to an integer cause from the above example, you'd consistently be off by one.  No good.  So in this case, I added another column (completely optional, you could just reuse the NumberOfPlayers integer column if you wanted to, I'm not to help illustrate my point).  Above you'll see the code using the Math.Round() method for the new column.

Now that you know how to correctly calculate averages, it's time to grab a coffee and get coding!

 

Resources:

Source: http://www.pchenry.com:8080/svn/blog/trunk/2010/IntDataColumnMathTest

MSDN: DataTable.Compute Method

PCHenry: Have you tried using Math.Round() before? Did you REALLY test it out?