Aggregate a Dataset

Question

How do i aggregate(using linq) the value of one column of a Dataset where the other column is the same.

For example, my columns are

I need something like this

edit: the values in "Row2" are the number of "Row1". so the values of (for example: 2) have to be aggregated into a new datatable where is only a single entry for (2). I'm sorry if the question is vague.

http://msdn.microsoft.com/en-us/vcsharp/aa336747.aspx#sumSimple — Shantanu Gupta, Aug 13 '10 at 10:41

score 5 · Accepted Answer · edited Aug 16 '10 at 06:59

5

You can use LINQ to aggregate rows and copy the results to a new table:

DataTable source = // whatever
DataTable dest = source.Clone();

var aggregate = source
    .AsEnumerable()
    .GroupBy(row => row.Field<int>("Row1"))
    .Select(grp => new { Row1 = grp.Key, Row2 = grp.Select(r => r.Field<int>("Row2")).Sum() });

foreach(var row in aggregate)
{
    dest.Rows.Add(new object[] { row.Row1, row.Row2 });
}

edited Aug 16 '10 at 06:59

abatishchev

98,240
88
296
433

answered Aug 13 '10 at 10:42

Lee

142,018
20
234
287

@Sam, @Lee: I strongly recommend you to use `IEnumerable.CopyToDataTable() where T : DataRow` http://msdn.microsoft.com/en-us/library/bb396189.aspx – abatishchev Aug 16 '10 at 06:56
How can we refactor this code to make it work for N number of columns ? – Karan May 08 '14 at 08:45
Please suggest for http://stackoverflow.com/questions/23537162/aggregate-datatable-with-dynamic-number-of-columns?noredirect=1#comment36108652_23537162 – Karan May 08 '14 at 09:18

abatishchev · Answer 2 · 2010-08-13T11:04:55.607

1

Add reference to System.Data.DataSetExtensions.dll

DataSet ds = ..
DataTable table = ds.Tables[0]; // or ds.Tables["YourTableName"];

var q = from row in table.AsEnumerable()
        group row by row.Field<int>("Row1") into g
        select new
        {
            Row1 = g.Key,
            Row2 = g.Sum(s => s.Field<int>("Row2"))
        };

If you need group by several columns:

var q = from record in table.AsEnumerable()
        group record by new { Row1 = record.Field<int>("Row1"), Row2 = record.Field<int>("Row3") } into g
        select new
        {
            Row1 = g.Key,
            Row2 = g.Sum(s => s.Field<int>("Row2"))
        };

edited Aug 13 '10 at 11:04

answered Aug 13 '10 at 10:43

abatishchev

98,240
88
296
433

@abatishchev: What is g.Key here. I was looking at link provided by me and was trying to understand it. Could you please provide some explanation on it. – Shantanu Gupta Aug 13 '10 at 10:46
@Shantanu: `g.Key` is a key by which you're grouping, in current case - `row.Field("Row1")` – abatishchev Aug 13 '10 at 10:47
@abatishchev: What if I had 3 or more columns in that case I had to do group by on all remaining columns. In that case how would it have worked. – Shantanu Gupta Aug 13 '10 at 10:48
@Shantanu: Very good post about nested grouping using LINQ: http://stackoverflow.com/questions/442425/nested-group-by-in-linq – abatishchev Aug 13 '10 at 10:59

score 0 · Answer 3 · answered Aug 13 '10 at 11:04

0

If you don't (want to) use Linq you can try this Microsoft HOWTO and create your own helper class.

answered Aug 13 '10 at 11:04

devnull

2,790
22
25

Aggregate a Dataset

3 Answers3