Sum datatable values by unique id and add to another column

Help Studio
, ,
1

Hello Community! :slight_smile:

I have a little question. I have datatable with 3 columns. ID - VALUE - SUM(empty)

ID VALUE SUM
XX - 5
XX - 15
XY - 10
XY - 20

I would like to sum “Value” column by “ID” column and paste it to “Sum” column

So it would be
ID - VALUE - SUM
XX - 5 - 20(5+15)
XX - 15 - 20(5+15)
XY - 10 - 30(10+20)
XY - 20 - 30(10+20)

I dont want to remove anything i want all rows at it is but with sum of collumn value added to all rows. Im trying something like below but it will remove duplicates and som in column “Value” when i want sum in column “Sum”

(From d In YourDatatableVariable.AsEnumerable
Group d By k=d("ID").toString.trim Into grp=Group
Let ra = New Object(){k,grp.Sum(Function (x) Convert.ToDouble(x("VALUE").toString.Trim))}
Select NewDatatableVariable.Rows.Add(ra)).CopyToDataTable
2

lets assume you have following input (Datatable without the SUM column
ID VALUE
XX - 5
XX - 15
XY - 10
XY - 20

And you want to get:
ID - VALUE - SUM
XX - 5 - 20
XX - 15 - 20
XY - 10 - 30
XY - 20 - 30

We can do:

prepare an empty datatable e.g. build datatable and modell the three cols id, value, sum -

NewDatatableVariable

Assign activity
NewDatatableVariable =

(From d In YourDatatableVariable.AsEnumerable
Group d By k=d("ID").toString.trim Into grp=Group
Let sm = grp.Sum(Function (x) Convert.ToDouble(x("VALUE").toString.Trim))
From g in grp
Order by YourDatatableVariable.Rows.IndexOf(g)
Let ra = g.ItemArray.Take(2).Append(s).toArray
Select r = NewDatatableVariable.Rows.Add(ra)).CopyToDataTable
1 Like
3

Thank you @Peter, you are the boss!

4

@ppr Peter, just one more thing. I were asking about it for example, but my project have 3 columns i want to sum:
Value, TaxValue, Tax

And i need 3 sum columns as well

ValueSum, TaxValueSum, TaxSum

Just like above but for three columns

I tried to adapt it like:

> (From d In YourDatatableVariable.AsEnumerable
> Group d By k=d("ID").toString.trim Into grp=Group
> Let sm = grp.Sum(Function (x) Convert.ToDouble(x("Value").toString.Replace(",",".").Replace(" ","").Trim))
> Let sn = grp.Sum(Function (x) Convert.ToDouble(x("TaxValue").toString.Replace(",",".").Replace(" ","").Trim))
> Let sb = grp.Sum(Function (x) Convert.ToDouble(x("Tax").toString.Replace(",",".").Replace(" ","").Trim))
> From g In grp
> Order By YourDatatableVariable.Rows.IndexOf(g)
> Let ra = g.ItemArray.Take(2).Append(sm).toArray
> Let rb= g.ItemArray.Take(3).Append(sn).toArray
> Let rc= g.ItemArray.Take(4).Append(sb).toArray
> Let ob = New Object(ra,rb, rc)
> Select r = NewDatatableVariable.Rows.Add(ob).CopyToDataTable
````Preformatted text`

So i tried to put 3 sum columns into object ob and then copy to datatable, but it is saying that there is too many arguments for object. Sorry to bother you, but i totally suck with Linq and just trying to learn it

5

just a sample

(From d In YourDatatableVariable.AsEnumerable
Group d By k=d("ID").toString.trim Into grp=Group
Let sm = grp.Sum(Function (x) Convert.ToDouble(x("Value").toString.Replace(",",".").Replace(" ","").Trim)) Let sn = grp.Sum(Function (x) Convert.ToDouble(x("TaxValue").toString.Replace(",",".").Replace(" ","").Trim))
Let sb = grp.Sum(Function (x) Convert.ToDouble(x("Tax").toString.Replace(",",".").Replace(" ","").Trim))
From g In grp
Order By YourDatatableVariable.Rows.IndexOf(g)
Let ra1 = g.ItemArray.Take(2).toArray
Let ra2= new Object(){sm,sn,sb}
Let ra = ra1.Concat(ra2).toArray
Select r = NewDatatableVariable.Rows.Add(ra).CopyToDataTable

Also have a look here:

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.