Group and Sum DataTable

Question

I have Data Table with the following data

Number   Type   Order  count
1        1      R       1
1        1      R       1
1        1      R       1
1        2      R       1

I am looking to get to this result

Number   Type   Order  count
1        1      R       3
1        2      R       1

How can I group by three columns

  var result = dt.AsEnumerable()
                 .GroupBy(x => {x.Field<string>("Number"))//need to group by Type and order  also need to sum te total counts

this may help You : https://stackoverflow.com/questions/16100900/select-multiple-fields-group-by-and-sum — Kuba Do, Aug 22 '19 at 08:53

score 6 · Accepted Answer · answered Aug 31 '19 at 04:57

rgoal

Your question made me curious, so I did some digging on Stack Overflow.
esc's answer appears will also solve your issue. It is posted under: How do I use SELECT GROUP BY in DataTable.Select(Expression)?:

Applying his method to your problem gave me this solution:

DataTable dt2 = dt.AsEnumerable()
    .GroupBy(r => new { Number = r["Number"], Type = r["Type"], Order = r["Order"] })
    .Select(g =>
    {
        var row = dt.NewRow();

        row["Number"] = g.Key.Number;
        row["Type"] = g.Key.Type;
        row["Order"] = g.Key.Order;
        row["Count"] = g.Count();

        return row;

    }).CopyToDataTable();

This will return a DataTable matching the schema of the input DataTable with the grouping and counts you requested.

Here is the full code I use to verify in LINQPad:

DataTable dt = new DataTable("Demo");

dt.Columns.AddRange 
(
    new DataColumn[] 
      {
         new DataColumn ( "Number", typeof ( int ) ),
         new DataColumn ( "Type", typeof ( int ) ),
         new DataColumn ( "Order", typeof ( string ) ),
         new DataColumn ( "Count", typeof ( int ) )  
      }
);

dt.Rows.Add(new object[] { 1,1,"R", 1 });
dt.Rows.Add(new object[] { 1,1,"R", 1 });
dt.Rows.Add(new object[] { 1,1,"R", 1 });
dt.Rows.Add(new object[] { 1,2,"R", 1 });


DataTable dt2 = dt.AsEnumerable()
    .GroupBy(r => new { Number = r["Number"], Type = r["Type"], Order = r["Order"] })
    .Select(g =>
    {
        var row = dt.NewRow();

        row["Number"] = g.Key.Number;
        row["Type"] = g.Key.Type;
        row["Order"] = g.Key.Order;
        row["Count"] = g.Count();

        return row;

    }).CopyToDataTable();


foreach (DataRow row in dt2.Rows)
{
    for (int i = 0; i < dt2.Columns.Count; i++)
        Console.Write("{0}{1}",
            row[i],                                                    // Print column data
            (i < dt2.Columns.Count - 1)? "  " : Environment.NewLine);  // Print column or row separator

}

Here are the results:

1  1  R  3
1  2  R  1

Group and Sum DataTable

1 Answers1