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

datatable
activities

#1

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)


Linq Group By and Sum
Assign : Input string was not in a correct format.Assign : Input string was not in a correct format
#2

@Dominic @ClaytonM @arivu96 @ovi


#3

@jamnanin, Refer this,

Regards,
Dom :slight_smile:


Data table sum into a cell
#4

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


#5

@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:


#6

I mean I am unable to follow it.


#7

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)


#8

this is the assign activity i have used.


#9

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


Total of excel column
#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


Sum values group by columns
Stuck to create a summary data from excel
How to identify duplicates in excel column?
Total of excel column
How to convert a table to pivot table with sum of a particular column
#11

thanks @balupad14 and @MAHESH1.

@MAHESH1 your solution was worked well thanks.


#12

Hi @jamnanin
Try this with less code.

Regards
Balamurugan


#13

hello,

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