CODEWITHSUNDEEP
c#asp.net
soccer7
soccer7

Reputation: 4005

Aggregate function on multiple columns in DataTable

I have data as following in DataTable

Date   Date1           Visits     M      F
1Jan   2015-01-01      0          0      0
1Jan   2015-01-01      0          0      0
1Jan   2015-01-01      10         7      3
2Jan   2015-01-02      2          2      0
2Jan   2015-01-02      15         10     5
3Jan   2015-01-03      20         17     3

I want to apply aggregate SUM function on Visits, M, F and GROUP BY Date and Date1. So that results would be-

Date    Date1        Visits     M      F
1Jan    2015-01-01   10         7      3
2Jan    2015-01-02   17         12     5
3Jan    2015-01-03   20         17     3

How could this be achieved using DataTables in C#

Upvotes: 1

Views: 2155

Answers (2)

Chandrasekar Kesavan
Chandrasekar Kesavan

Reputation: 795

Try this; You can see the SUM result in dtNew(DataTable).

DataTable dtNew =new DataTable("NewDataTable");
dtNew.Columns.Add("Date");
dtNew.Columns.Add("Date1");
dtNew.Columns.Add("Visits",typeof(int));
dtNew.Columns.Add("M",typeof(int));
dtNew.Columns.Add("F",typeof(int));


dt.AsEnumerable()
  .GroupBy(grp => new
    {
       Date = grp.Field<string>("Date"),
       Date1 = grp.Field<string>("Date1")
    })
   .Select(d =>
       {
         DataRow dr = dtNew.NewRow();
         dr["Date"] = d.Key.Date;
         dr["Date1"] = d.Key.Date1;
         dr["Visits"] = d.Sum(r => r.Field<int>("Visits"));
         dr["M"] = d.Sum(r => r.Field<int>("M"));
         dr["F"] = d.Sum(r => r.Field<int>("F"));
         return dr;
       })
    .CopyToDataTable(dtNew, LoadOption.OverwriteChanges);

Upvotes: 1

Phil
Phil

Reputation: 125

You can get some way using LINQ to DataSet. The following assumes dt is a DataTable with columns named "Day", "Visits", "M" and "F":

var groupedByDay = dt.AsEnumerable().GroupBy(r => r.Field<string>("Day"));
foreach (var day in groupedByDay)
{
    var sumVisits = day.Select(r => r.Field<int>("Visits")).Sum();
    var sumMs = day.Select(r => r.Field<int>("M")).Sum();
    var sumFs = day.Select(r => r.Field<int>("F")).Sum();
    Console.WriteLine("{0}\t{1}\t{2}\t{3}", day.Key, sumVisits, sumMs, sumFs);
}

Upvotes: 1

Related Questions