How group by and sum values in DataTable?

  • Thread starter Thread starter Ronald S. Cook
  • Start date Start date
R

Ronald S. Cook

I have a DataTable that looks like this:

Id Value
123 4.0
123 5.0
234 1.0
345 2.0
345 3.0

I want to end up with (probably a new DataTable) that contains the sum of
values grouped by Id like this:

Id SumOfValue
123 9.0
234 1.0
345 5.0

Anyone know how to write this?

Thanks!
 
Ronald S. Cook said:
I have a DataTable that looks like this:

Id Value
123 4.0
123 5.0
234 1.0
345 2.0
345 3.0

I want to end up with (probably a new DataTable) that contains the sum of
values grouped by Id like this:

Id SumOfValue
123 9.0
234 1.0
345 5.0

Anyone know how to write this?
Click to expand...

There is no standard functionality in the DataTable itself to do this. If
you can use LINQ, then the groupby clause will do the aggregation for you.
Otherwise, you will have to sort the DataTable on the Id column, and then
loop through the rows adding the Value column until the Id changes, and then
dump the current Id and accumulated value onto the new table and repeat
until all the rows have been processed.
 
To show the LINQ approach (note most of this code is setting up the
table to demo with):

using System;
using System.Data;
using System.Linq;

class Program
{
static void Main(string[] args)
{
DataTable table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Value", typeof(decimal));
table.Rows.Add(123, 4.0M);
table.Rows.Add(123, 5.0M);
table.Rows.Add(234, 1.0M);
table.Rows.Add(345, 2.0M);
table.Rows.Add(345, 3.0M);

var query = from row in table.AsEnumerable()
group row by row.Field<int>("Id") into grp
orderby grp.Key
select new
{
Id = grp.Key,
Sum = grp.Sum(r => r.Field<decimal>("Value"))
};

foreach (var grp in query)
{
Console.WriteLine("{0}\t{1}", grp.Id, grp.Sum);
}
}
}
 
Back
Top