Click here to Skip to main content
15,896,726 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My datatable has a column called TotalAmount. To get grand total I have to sum all the value in that column. That's easy:

C#
var sum = dt.Compute("Sum(TotalAmount)","");


Now I need to create another column called PercentTotal which is mathematically speaking TotalAmount/sum .

Is there a datatable method which allows me to express this more easily rather than iterate the rows and compute value for that specific column everytime.
Posted
Updated 3-Jan-14 7:07am
v2
Comments
Maciej Los 3-Jan-14 13:00pm    
Not clear! Please be more specific and provide more details (sample data).
deostroll 3-Jan-14 13:17pm    
It is the mathematical equivalent of summing a set of values, and weighting each value with the summed value.

For e.g. 4, 5, 6. Sum = 15.

The weighted set now becomes something like 4/15, 5/15, 6/15

Hope it makes sense now.

It can be done as follows:

C#
var sum = dt.Compute("Sum(TotalAmount)","");

var dc = new DataColumn ("PercentTotal", 
    typeof (double), 
    "TotalAmount / " + sum.ToString ());

dt.Columns.Add (dc);
 
Share this answer
 
v2
If dt is a Data.DataTable object, you can use something like that:
C#
//get System.Data.EnumerableRowCollection
var values = dt.AsEnumerable();
//get total from <code>myVal</code> field
int total = values.Sum(r=>r.Field<int32>("myVal"));
//do calculations using linq query
var PercSum = from v in values 
              select new
              {
                  Value = v.Field<int32>("myVal"),
                  SumOfValue = total,
                  Percentage = Convert.ToDecimal(v.Field<int32>("myVal")) / Convert.ToDecimal(total)
               };
//list values 
Console.WriteLine("Value | Sum | Percentage");
foreach (var p in PercSum )
{
    //
    Console.WriteLine("{0} | {1} | {2}", p.Value.ToString(), p.SumOfValue.ToString(),p.Percentage);
}
 
Share this answer
 
v2
Comments
idenizeni 3-Jan-14 18:16pm    
Nice! Your solution would work in many more cases than mine.
Maciej Los 3-Jan-14 18:27pm    
Thank you ;)
Yes, you can do this by adding a column to your DataTable that has an expression defined.
Like so...
C#
DataTable tbl = new DataTable("TestTable");

DataColumn c = new DataColumn("Col1", typeof(Int32));
tbl.Columns.Add(c);
c = new DataColumn("Col2", typeof(Int32));
tbl.Columns.Add(c);

// create expression data column and add to table
// the third parameter defines the expression.
c = new DataColumn("Col3", typeof(Decimal), "Col1 / Col2");
tbl.Columns.Add(c);

for (int i = 1; i <= 10; i++)
{
    DataRow r = tbl.NewRow();
    r["Col1"] = i;
    r["Col2"] = (i + 10);
    // don't set Col3 as the expression will automatically used to set the value
    tbl.Rows.Add(r);
}
 
Share this answer
 
Comments
Maciej Los 3-Jan-14 17:02pm    
Nice, +5!
Have a look at my solution ;)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900