How group by and sum values in DataTable? RSS

• • Reply
  

  Jags_464

  Member

  56 Points

  20 Posts

  How group by and sum values in DataTable?

  Jun 19, 2010 02:13 PM|LINK

  How group by and sum values in DataTable??
  
  I have a DataTable that looks like this:
  
  Id   Value
  1    4.0
  1    5.0
  3    1.0
  2    2.0
  3   3.0
  
  I want to end up with (probably a new DataTable) that contains the sum of
  values grouped by Id like this:
  
  Id SumOfValue
  1 9.0
  2 2.0
  3 4.0
  
  pls help me.......
  
  Thanks!

  
  

  datatable dataset

  -Jags
• • Reply
  

  MarkRae

  Member

  294 Points

  77 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 03:03 PM|LINK
  --
  Mark Rae
  ASP.NET MVP
  http://www.markrae.net
• • Reply
  

  PeteNet

  All-Star

  81342 Points

  11398 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 03:06 PM|LINK

  here's how you would do it with a query and a bit of Reflection:

  using System.Data;
  using System.Reflection;

  protected void Page_Load(object sender, EventArgs e)
          {
              DataTable dt = new DataTable();
              dt.Columns.Add(new DataColumn("ID", typeof(Int32)));
              dt.Columns.Add(new DataColumn("Value", typeof(Decimal)));
              dt.Rows.Add(1, 4.0M);
              dt.Rows.Add(1, 5.0M);
              dt.Rows.Add(3, 1.0M);
              dt.Rows.Add(2, 2.0M);
              dt.Rows.Add(3, 3.0M);
  
  
              var query = from r in dt.AsEnumerable()
                          group r by r.Field<int>(0) into groupedTable
                          select new
                          {
                              id = groupedTable.Key,
                              sumOfValue = groupedTable.Sum(s => s.Field<decimal>("Value"))
                          };
  
  
  
  
              DataTable newDt = ConvertToDataTable(query);
  
  
          }
  
  
          public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist)
          {
              DataTable dtReturn = new DataTable();
  
  
              // column names
              PropertyInfo[] oProps = null;
  
  
              if (varlist == null) return dtReturn;
  
  
              foreach (T rec in varlist)
              {
                  // Use reflection to get property names, to create table, Only first time, others will follow
                  if (oProps == null)
                  {
                      oProps = ((Type)rec.GetType()).GetProperties();
                      foreach (PropertyInfo pi in oProps)
                      {
                          Type colType = pi.PropertyType;
  
  
                          if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                          {
                              colType = colType.GetGenericArguments()[0];
                          }
  
  
                          dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                      }
                  }
  
  
                  DataRow dr = dtReturn.NewRow();
  
  
                  foreach (PropertyInfo pi in oProps)
                  {
                      dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                      (rec, null);
                  }
  
  
                  dtReturn.Rows.Add(dr);
              }
              return dtReturn;
          }

  
  
  

  datatable sum group by

  Regards,
  Peter
• • Reply
  

  MarkRae

  Member

  294 Points

  77 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 03:23 PM|LINK
  --
  Mark Rae
  ASP.NET MVP
  http://www.markrae.net
• • Reply
  

  PeteNet

  All-Star

  81342 Points

  11398 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 03:40 PM|LINK

  MarkRae

  The Compute method of the DataTable object does all of the above in one line of code...

  The Compute method would, yes, sum up on the current rows for 'values'...and maybe throw in a filter.

  How would you return a sum of values grouped by ID into a new DataTable with distinct rows on the ID? (oh, and in a single line of code...even though I think a single line of code is not of any real interest here)
  

  
  

  Regards,
  Peter
  • Edited by PeteNet on Jun 19, 2010 03:41 PM
• • Reply
  

  MarkRae

  Member

  294 Points

  77 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 04:19 PM|LINK
  --
  Mark Rae
  ASP.NET MVP
  http://www.markrae.net
• • Reply
  

  PeteNet

  All-Star

  81342 Points

  11398 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 04:29 PM|LINK

  MarkRae

  <div>"PeteNet" wrote in message news:3935337@forums.asp.net...</div>

  
  

  <div> MarkRae:</div> <div>The Compute method of the DataTable object does all of the above in one line of code...</div>

  
  

  The Compute method would, yes, sum up on the current rows for 'values'...and maybe throw in a filter.

  How would you return a sum of values grouped by ID into a new DataTable with distinct rows on the ID? (oh, and in a single line of code...even though I think a single line of code is not of any real interest here)
  

  
  

  ---

  Regards,
  Peter
  

  ---

  http://forums.asp.net/p/1570562/3935337.aspx#3935337
  
  

  My mistake - apologies.
  

  <div> </div> <div>
  --
   </div>

  Mark, accepted.

  Thank you.

  
  

  
  

  Regards,
  Peter
• • Reply
  

  MarkRae

  Member

  294 Points

  77 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 04:37 PM|LINK
  --
  Mark Rae
  ASP.NET MVP
  http://www.markrae.net
• • Reply
  

  PeteNet

  All-Star

  81342 Points

  11398 Posts

  Re: How group by and sum values in DataTable?

  Jun 19, 2010 04:50 PM|LINK

  MarkRae

  <div>This is as close as I could get:</div> <div> </div> <div>

  DataTable objDT1 = new DataTable();

  objDT1.Columns.Add("ID", typeof(int));

  objDT1.Columns.Add("Value", typeof(decimal))

  .....etc
  

  </div>

  good attempt. :)

  ..that was the kind of solution I was offering in the first place.

  
  

  ...and then we can take it further per this requirement:

  Jags_464

  I want to end up with (probably a new DataTable)

  and convert the sequence into a DataTable.

  the public function I used:

  public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist) 

  is normally used as an Extension method which effectively adds it as a method on the object.
  

  ..and finally, goes give Jags_464 exactly what he requires.
  

  Regards,
  Peter
• • Reply
  

  Jags_464

  Member

  56 Points

  20 Posts

  Re: How group by and sum values in DataTable?

  Jun 23, 2010 06:57 AM|LINK

  Thanks for ur replies... I got the solution to the problem... I first sorted datatable based on the Id.. then i copied those rows having same Id to having same id to a DataRow[] by runnig Dttable.Select("Query")  and then i run a foreach loop through the DataRow[] to Compute the Sum...
  

  -Jags
  • Marked as answer by Jags_464 on Jun 23, 2010 06:57 AM