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 understanding.group and sum.xlsx (9.7 KB)

@Dominic @ClaytonM @arivu96 @ovi

@jamnanin, Refer this,

Regards,
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.

@jamnanin,

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.

Regards,
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

Hi,
Can you check this. It helps you.

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

Regards
Balamurugan.S

1 Like

@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

thanks @balupad14 and @MAHESH1.

@MAHESH1 your solution was worked well thanks.

Hi @jamnanin
Try this with less code.

Regards
Balamurugan

1 Like

hello,

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