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)
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 
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
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)
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
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