Divide column with external value

Question

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 3-Jan-14 6:45am

deostroll

Updated 3-Jan-14 7:07am

v2

Add a Solution

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.

3 solutions

Solution 1

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);
}

Posted 3-Jan-14 8:55am

idenizeni

Comments

Maciej Los 3-Jan-14 17:02pm

Nice, +5!
Have a look at my solution ;)

Solution 2

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);
}

Posted 3-Jan-14 11:00am

Maciej Los

Updated 3-Jan-14 11:01am

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 ;)

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)

deostroll · Accepted Answer · 2014-01-03T18:20:00

Solution 3

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);

Posted 3-Jan-14 18:20pm

deostroll

Updated 3-Jan-14 18:26pm

v2