Using Linq to Groupby and Sum Datatable

Using Linq to GroupBy and Sum datatable

You can GroupBy first, then project the groups to DataRows, 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();

Using Linq to GroupBy and Sum datatable

You can GroupBy first, then project the groups to DataRows, 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();

Group and Sum DataTable

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

Convert Datatable GroupBy Multiple Columns with Sum using Linq

You can use this:-

DataTable countriesTable = dt.AsEnumerable().GroupBy(x => new { CountryId = x.Field<int>("CountryId"), CityId = x.Field<int>("CityId") })
                             .Select(x => new Countries
                                          {
                                              CountryId = x.Key.CountryId,
                                              CityId = x.Key.CityId,
                                              TotalSum = x.Sum(z => z.Field<int>("TotalImages"))
                                          }).PropertiesToDataTable<Countries>();

I am getting, following output:-

Since, We cannot use CopyToDataTable method for anonymous types, I have used an extension method took from here and modified it accordingly.

public static DataTable PropertiesToDataTable<T>(this IEnumerable<T> source)
    {
        DataTable dt = new DataTable();
        var props = TypeDescriptor.GetProperties(typeof(T));
        foreach (PropertyDescriptor prop in props)
        {
            DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
            dc.Caption = prop.DisplayName;
            dc.ReadOnly = prop.IsReadOnly;
        }
        foreach (T item in source)
        {
            DataRow dr = dt.NewRow();
            foreach (PropertyDescriptor prop in props)
            {
                dr[prop.Name] = prop.GetValue(item);
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }

And, here is the Countries type:-

public class Countries 
{
    public int CountryId { get; set; }
    public int CityId { get; set; }
    public int TotalSum { get; set; }
}

You can use any other approach to convert it to a DataTable if you wish.

Datatable group by sum

table1.AsEnumerable().Concat(table2.AsEnumerable())
      .GroupBy(r => r.Field<string>("Name"))
      .Select(g => new {
          Name = g.Key,
          Rank1 = g.Count(x => x.Field<int>("Rank") == 1),
          Rank2 = g.Count(x => x.Field<int>("Rank") == 2),
          Rank3 = g.Count(x => x.Field<int>("Rank") == 3),
          OtherRank = g.Count(x => x.Field<int>("Rank") > 3)
      }).CopyToDataTable();

You will need implementation of CopyToDataTable method where Generic Type T Is Not a DataRow.

---

A little optimized solution (single parsing and single loop over grouped ranks):

(from row in table1.AsEnumerable().Concat(table2.AsEnumerable())
 group row by row.Field<string>("Name") into g
 let ranks = g.Select(x => x.Field<int>("Rank")).ToList()
 select new {
    Name = g.Key,
    Rank1 = ranks.Count(r => r == 1),
    Rank2 = ranks.Count(r => r == 2),
    Rank3 = ranks.Count(r => r == 3),
    OtherRank = ranks.Count(r => r > 3)        
 }).CopyToDataTable();

c# datatable groupby and sum column's values (without know the name)

So assuming you have at least the list of column names, I'd go with the approach of creating a dictionary as part of the select and then transform it later to whatever form you need it. Here's an example:

var query = from stat in stats
                        group stat by stat.Field<string>("Data") into data
                        orderby data.Key
                        select new
                        {
                            Data = data.Key,
                            SumsDictionary = listOfColumnNames
                                             .Select(colName => new { ColName = colName, Sum = data.Sum(stat => stat.Field<int>(colName)) })
                                             .ToDictionary(d => d.ColName, d => d.Sum),
                            Productivity = 0,
                        };

So that if you were to serialize the result object it would look something like this:

{
  "Data": {},
  "SumsDictionary": {
    "Time_Work": 10,
    "Time_Hold": 20,
    "Time_Alarm": 30
  },
  "Productivity": 0
}

Hope it helps!

C# Datatable group, select and sum

If the fixed salary is always the same for the Name, you can use this :

var result = from tab in dtTemp.AsEnumerable()
                     group tab by tab["Name"]
                     into groupDt
                     select new
                     {
                         Agent = groupDt.Key,
                         Commision = groupDt.Sum((r) => decimal.Parse(r["Amount"].ToString())),
                         FixedSalary = groupDt.Select(r => r["FixedSalary"].ToString()).FirstOrDeafult()
                     };

İf it can be change, you should group it with the name column.

c# DataTable Linq Group by Multiple column sum int and timespan

You have to Parse the TimeSpan and Sum by using Ticks, like below.

var Temp_DT = tempdt.AsEnumerable().Select(x =>
                    new
                    {
                        UID = x["UID"],
                        EMPNAME = x["Emp Name"],
                        EMPROLE = x["Role"],
                        SUPID = x["Sup ID"],
                        SUPNAME = x["Sup Name"],
                        DESIGNATION = x["Designation"],
                        VOLUME = x["Volume"],
                        ERRORTIME = x["Error_Time"],                            
                        ACWTIME = x["ACW"],
                        BREAKTIME = x["Break Time"],
                        IDLETIME = x["Idle"],
                        NONPRODUCTIVE = x["Non Productive"],
                    }).GroupBy(s => new { s.UID, s.EMPNAME, s.EMPROLE, s.SUPID, s.SUPNAME, s.DESIGNATION })
                    .Select(g => {
                      var grouped = g.ToList();
                      return new
                      {
                          UID = g.Key.UID,
                          EMPNAME = g.Key.EMPNAME,
                          EMPROLE = g.Key.EMPROLE,
                          SUPID = g.Key.SUPID,
                          SUPNAME = g.Key.SUPNAME,
                          DESIGNATION = g.Key.DESIGNATION,
                          VOLUME = grouped.Sum(x => Convert.ToInt16(x.VOLUME)),
                          Error_Time = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.ERRORTIME.ToString())).ToList().Sum(r=> r.Ticks)),
                          ACW = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.ACWTIME.ToString())).ToList().Sum(r=> r.Ticks)),
                          Break = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.BREAKTIME.ToString())).ToList().Sum(r=> r.Ticks)),
                          Idle = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.IDLETIME.ToString())).ToList().Sum(r=> r.Ticks)),
                          NonProductive = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.NONPRODUCTIVE.ToString())).ToList().Sum(r=> r.Ticks))
                      };
                    }).ToList();

The Conversion method is,

private static TimeSpan ConvertTimeSpan(string str)
{
   TimeSpan outputValue; 
   TimeSpan.TryParse(str, out outputValue);

   return outputValue;
}

C# Fiddle with sample data.

---

Related Topics