# How group by and sum values in DataTable? RSS

## 10 replies

Last post Aug 18, 2010 12:04 AM by snteran

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   Value1    4.01    5.03    1.02    2.03   3.0I want to end up with (probably a new DataTable) that contains the sum ofvalues grouped by Id like this:Id SumOfValue1 9.02 2.03 4.0pls help me.......Thanks!`

datatable dataset

-Jags

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

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

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

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

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

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

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

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

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