# How to use group by and get sum?

Hi Everyone,

I need help about my automation problem.

Right now I have the following data below:

As you can see I would like to check if grouping the column Store code and adding sum is possible in UiPath.

Regards

AA

Welcome to the community.

1. Store your input data in a data table called dt1
2. Create one more datable called dt2 where can store output. Build this data table with two columns namely STORE CODE, TOTAL
3. Now use below linq
(From row in dt1.AsEnumerable
Group row by sc = row(â€śSTORE CODEâ€ť).ToString()
Let total= grp.sum(Function (x) Convert.ToDouble(x(â€śCREDITâ€ť).ToString().Replace(â€ś\$â€ť,â€śâ€ť).Trim()))
Let result = New-Object() {grp(0)(â€śSTORE CODEâ€ť),total}

`grp.sum(Function...)` I dont see `grp` being declared. Do you mean to refer `sc`
Just wondering how would linq know to refer to a keyword `grp`. Is it a reserved keyword in linq, which we can use when grouping?

Should it not be like below?

``````(From row in dt1.AsEnumerable
Group row by sc = row(â€śSTORE CODEâ€ť).ToString()
Let total= sc.sum(Function (x) Convert.ToDouble(x(â€śCREDITâ€ť).ToString().Replace("\$","").Trim()))
Let result = New-Object() {sc(0)(â€śSTORE CODEâ€ť),total}
``````
1 Like

How will I encode the linq code?

I am going to do assign activity?

Regards,

AA

Hi @jeevith

From my knowledge, even sc is a temporary variable used to loop through the particular column and group the rows,

We have to assign the grouped rows into a variable by using into grp = Groupâ€¦now grp (each item will have multiple rows) variable will have the grouped rows

``````(From row in dt1.AsEnumerable
Group row by sc = row(â€śSTORE CODEâ€ť).ToString() into grp = Group
Let total= grp.sum(Function (x) Convert.ToDouble(x(â€śCREDITâ€ť).ToString().Replace("\$","").Trim()))
Let result = New-Object() {grp(0)(â€śSTORE CODEâ€ť),total}
``````

Thanks

That is good to know. So `grp` has to be declared to be of type Group before we perform operations on it. In into grp = Group I am assuming `Group` is a reserved word in linq.

I am learning linq only by reading posts in the community, so wanted to know how this particular query works. Thank you for the explanation.

2 Likes

Hi @jeevith

@prasath_S is correct. I forgot to use into grp=Group

1 Like

Yes. you should use assign activity.

In the left hand side of assign acitivity you should use dt2, in the right hand side you should use Linq.

Also see below updated linq:
(From row in dt1.AsEnumerable
Group row by sc = row(â€śSTORE CODEâ€ť).ToString()
Into grp = Group
Let total= grp.sum(Function (x) Convert.ToDouble(x(â€śCREDITâ€ť).ToString().Replace(â€ś\$â€ť,â€śâ€ť).Trim()))
Let result = New-Object() {grp(0)(â€śSTORE CODEâ€ť),total}