CODEWITHSUNDEEP
c#linqdatatablegroup-bysum
Wang Jijun
Wang Jijun

Reputation: 326

How to sum values for multiple columns in DataTable using Groupby with Linq

I have a simple DataTable as the following:

column1 value1  value2
ee      1       2
ee      2       4
ee1     3       6
ee2     3       3
ee2     4       2

I want to get the sum of value1 and value2 by each colunm1, lastly get a new DataTable as the following:

column1 value1  value2
ee      3       6
ee1     3       6
ee2     7       5

Its not too difficult to realize by a loop of calculation in a traditional way. I want to realize that as simple as possible in a Linq way. I have a existed solution, but I dont like it very much.Especially, here I had to use the Foreach to realize it, it`s a little weird. 

var resultDt = dtIn.Clone();
dtIn.Select().GroupBy(m=>m["column1"].ToString())
             .Select(n=>new 
                          { Row = n.First(),
                            Value1 = n.Sum(s=>(int)s["value1"]),
                            Value2 = n.Sum(s=>(int)s["value2"])
                          })
             .ToList()
             .ForEach(m=>
                      {
                          var drNew = m.Row;
                          drNew["value1"] = m.Value1; 
                          drNew["value2"] = m.Value2;
                          resultDt.Rows.Add(drNew);
                      });

Somebody who can give me a hint?

Upvotes: 1

Views: 6865

Answers (3)

Slai
Slai

Reputation: 22896

The smarter way is usually to do the aggregate on the database side to avoid filling unnecessary data.

The loop can be hidden in a .Aggregate:

var result = dt.Rows.Cast<DataRow>().ToLookup(r => r[0] as string).Aggregate(dt.Clone(),
    (t, g) => t.Rows.Add(g.Key, g.Sum(r => (int)r[1]), g.Sum(r => (int)r[2])).Table);

or a bit more complicated for multiple columns:

DataTable result = dt.Rows.Cast<DataRow>().ToLookup(r => r[0])
    .Aggregate(dt.Clone(), (t, g) => t.Rows.Add(Enumerable.Range(0, t.Columns.Count)
    .Select(i => i < 1 ? g.Key : g.Sum(r => (int)r[i])).ToArray()).Table);

Debug.Print(string.Join("\t", result.Columns.Cast<DataColumn>().Select(c => c.ColumnName)));
Debug.Print(string.Join("\n", result.Rows.Cast<DataRow>().Select(r => string.Join("\t\t", r.ItemArray))));

Upvotes: 0

Damith
Damith

Reputation: 63105

using CopyToDataTable 

var resultDt = dtIn.AsEnumerable().GroupBy(m=>m.Field<string>("column1"))
                 .Select(n=>new 
                              { column1  = n.Key,
                                Value1 = n.Sum(s=>s.Field<int>("value1")),
                                Value2 = n.Sum(s=>s.Field<int>("value2"))
                              }).CopyToDataTable();

Upvotes: 3

Sadique
Sadique

Reputation: 22841

Here give this a try, this should work for you:

DataTable dt = new DataTable();
dt.Columns.Add("column1");
dt.Columns.Add("value1", typeof(int));
dt.Columns.Add("value2", typeof(int));

dt.Rows.Add("ee", 1, 2);
dt.Rows.Add("ee", 2, 4);
dt.Rows.Add("ee1", 3, 6);
dt.Rows.Add("ee2", 3, 3);
dt.Rows.Add("ee2", 4, 2);

var items = dt.AsEnumerable().GroupBy(
            x => x.Field<string>("column1")
    ).Select
    (
        n => new
        {
            column1 = n.Key,
            value1 = n.Sum(z => z.Field<int>("value1")),
            value2 = n.Sum(z => z.Field<int>("value2"))
        }
    )
    .ToList();

Upvotes: 1

Related Questions