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

How do I group by one column in the datatable and take the sum of another column based on this grouping. I have attached a sample file for better and sum.xlsx (9.7 KB)

@Dominic @ClaytonM @arivu96 @ovi

@jamnanin, Refer this,

Dom :slight_smile:

I did read this but i am unable to execute it. could you provide a work flow? It would be very helpful.


good that you have done this :slight_smile:

Try from your end and upload the xaml here. Paste the error you derive as it would be helpful for the people who looks and get this issue.

Dom :slight_smile:

I mean I am unable to follow it.

I also tried the .compute method but i get this error.

consolidateTableCompute has thrown an exception

Source: Assign

Message: Invalid usage of aggregate function Sum() and Type: Object.

Exception Type: DataException

System.Data.DataException: Invalid usage of aggregate function Sum() and Type: Object.
at System.Data.Common.ObjectStorage.Aggregate(Int32 records, AggregateType kind)
at System.Data.DataColumn.GetAggregateValue(Int32 records, AggregateType kind)
at System.Data.AggregateNode.Eval(Int32 records)
at System.Data.DataExpression.Evaluate(DataRow rows, DataRowVersion version)
at System.Data.DataTable.Compute(String expression, String filter)
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

this is the assign activity i have used.

1 Like

Can you check this. It helps you.

file : group by one column in the datatable and take the (3.1 KB)


1 Like


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

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

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



thanks @balupad14 and @MAHESH1.

@MAHESH1 your solution was worked well thanks.

Hi @jamnanin
Try this with less code.


1 Like


i cann’t found the “Amount” in the LisA assignation