[SOLVED] Groupby and Sum from a datatable or dataview

Groupby and Sum from a datatable or dataview

I have a datatable (filled from xls file) with different sort of fruits and legumes.

I make a dataview from this datatable, and using RowFilter to only have the potatoes and tomatoes.

Now, I try to make a groupby (with column 'Name') and making a sum from another column 'Quantity'.

Actually :

Name    |  Quantity  | color
tomatoe |     2      |  red
tomatoe |     1      |  red
potatoe |     5      |  yellow
tomatoe |     1      |  red
potatoe |     1      |  yellow

I would return a datatable or dataview like this :

Name    |  Quantity | color
tomatoe |     4     |  red
potatoe |     6     |  yellow

how can I do that ?

Sorry if this is simple, but I'm noob with that.

Solution

Use LINQ, in this case Linq-To-DataTable:

Dim fruitGroups = table.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Name"))

Now you can create the result table:

Dim tableResult = table.Clone() ' empty table with same columns
For Each grp In fruitGroups 
    tableResult.Rows.Add(grp.Key, grp.Sum(Function(row) row.Field(Of Int32)("Quantity")), grp.First().Field(Of String)("Color"))
Next

That will take an arbitrary color of each group(the first). So if you want to group by the combination of Name and Color you have to group by an anonymous type containg both:

Dim fruitGroups = table.AsEnumerable().
    GroupBy(Function(row) New With {
        Key .Name = row.Field(Of String)("Name"),
        Key .Color = row.Field(Of String)("Color")
    })

Dim tableResult = table.Clone()
For Each grp In fruitGroups
    tableResult.Rows.Add(grp.Key.Name, grp.Sum(Function(row) row.Field(Of Int32)("Quantity")), grp.Key.Color)
Next

If you need a DataView you can use tableResult.DefaultView.