How do I group by one column in the datatable and take the sum?

Help
,
10

@jamnanin

I will suggest you one more way try like this.
Let us take your excel stored in a datatable dta

First group the datatable based on ID by using below Query and assign it to dt1
dt1=(From p In dta.Select()
Group p by ID=p.Item(“ID”).ToString Into Group
Select Group(0)).ToArray.CopyToDataTable()

Now take only two columns by using below query
dt1=dt1.DefaultView.ToTable(False,“ID”,“Name”)

Now Add one datacolumn to dt1 by using add datacolumn activity and give name Amount to that column

Then create one list of strings ListA

Then use this query
ListA=(From p In dta.Select()
Group p By ID=p.Item(“ID”).ToString Into GroupA=Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“Amount”).ToString)))).ToList()

Next Run one Foreach loop for dt1

Inside for each use one assign activity
row(“Amount”)=ListA(dt1.Rows.IndexOf(row)).ToString

Now your dt1 will contain the required output and you can write to excel by using WriteRange.

Regards,
Mahesh

20 Likes