Using Linq to GroupBy and Sum datatable
Solution 1
You can GroupBy
first, then project the groups to DataRow
s, then create the DataTable
using CopyToDataTable
extension:
var newDt = dt.AsEnumerable()
.GroupBy(r => r.Field<int>("Id"))
.Select(g =>
{
var row = dt.NewRow();
row["Id"] = g.Key;
row["Amount 1"] = g.Sum(r => r.Field<int>("Amount 1"));
row["Amount 2"] = g.Sum(r => r.Field<int>("Amount 2"));
row["Amount 3"] = g.Sum(r => r.Field<int>("Amount 3"));
return row;
}).CopyToDataTable();
Solution 2
You are making your calls out of order. The code below wont put the data into a table, but it will give you data you could put into a table easily.
dt.AsEnumberable()
.GroupBy(g => g["ID])
.Select(g => new {
Id = g.Key,
Amount1 = g.Sum(s => s.Amount1),
Amount2 = g.Sum(s => s.Amount2),
Amount3 = g.Sum(s => s.Amount3)});
Solution 3
This works for me. A slight Change to Arturo's code to help with cast issues if you have decimal values and use a datatable as a datatable not a variable. (Note that r[15] is the column you will be summing)
dt = dt.AsEnumerable()
.GroupBy(r => r["fldReportCode"])
.Select(g =>
{
var row = dt.NewRow();
row["fldReportCode"] = g.Key;
row[15] = g.Sum(r => (decimal)r[15]);
//row["Amount 2"] = g.Sum(r => r.Field<int>("Amount 2"));
//row["Amount 3"] = g.Sum(r => r.Field<int>("Amount 3"));
return row;
}).CopyToDataTable();
Leon Winston
Updated on September 05, 2020Comments
-
Leon Winston over 3 years
Hi, I have a Datatable like this:
Id Amount 1 Amount 2 Amount 3 1 2 2 2 12 4 6 4 12 6 6 5 22 7 2 1 22 7 2 2
I need to get my datatable like this:
Id Amount 1 Amount 2 Amount 3 1 2 2 2 12 10 12 9 22 14 4 3
I originally tried to do it in an anonymous method but I need to return it to another class which cannot be done with anonymous method. My second attempt was to do this so it can be returned:
DataTable ddt = dt.AsEnumerable() .Sum(g => g.Field<int>("Amount 1")) .GroupBy(g => new { Col1 = g["ID"] }) .Select(g => g.OrderBy(r => r["ID"]).First()) .CopyToDataTable();
This code definitely wont compile but Any help/advice if possible would be really appreciated. I'm very new to linq.
-
KiMaN about 5 yearsHi, I used this code but it empties the dt table and only keeps the line added ?!
-
Çağatay Kaya almost 4 yearsHello this solution work on Android and also windows build but when i build for iOS it throw Not Supported Exception as in this question.System.Data throws NotSupportedException on iOS Unity Build Could you give and answer to this question