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
Fetch the repeated string from excel cell
#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
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
Stuck to create a summary data from excel
#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


closed #14