How to sum up the duplicates values

Help Studio
, ,
1

In the below example i want column fff only PA2 values to be sum up based on column aaa values which are duplicate .

aaa bbb ccc ddd Amount fff
1 1 1 0 656.16 PA2
1 1 1 0 187.5 PA2
3 2 1 0 137.5 PA2
10 2 1 0 137.5 PA2
3 1 1 0 187.5 PA2
6 1 1 0 137.5 PA2
7 1 1 0 356.62 PA4
8 1 1 0 356.62 PA4
9 1 1 0 356.62 PA4
9 2 1 0 356.62 PA4
11 1 1 0 356.62 PA4
12 2 1 0 356.62 PA4
2

Hi @T_Y_Raju

give a try with the following

yourDTVar.AsEnumerable.GroupBy(Function(x) x("yourColumn")).Select(Function(r) r.First).CopyToDataTable()

Regards

3

Hi @T_Y_Raju ,

Could you also provide us with the Expected Output table for the Input provided to confirm on the logic that needs to be applied ?

4
aaa bbb ccc ddd Amount fff
1 1 1 0 843.66 PA2
3 2 1 0 325 PA2
10 2 1 0 137.5 PA2

i have taken the example of first five rows data

5

Hello @T_Y_Raju, So based on your last message.

You need to sum the value based on the column “aaa”

Am I, Right?

6

yes i have to sum based on column aaa(duplicate values) and column fff(PA2) i dont want to sum PA4 values even if there are duplicates.

7

Hi @T_Y_Raju ,

First you will have to filter the data based on the fff column to consider only rows containing PA2 and then you can use the following code to get the sum of the duplicate columns. add this in the assign statement and add your datatable instead of dt
(From row In dt.AsEnumerable
Group row By key = New With { Key.group = row.Item(“aaa”).ToString.Trim
} Into grp = Group
Select dt.LoadDataRow(New Object() {
Key.group,
grp.Select(Function(x) x(“bbb”).ToString).First,
grp.Select(Function(x) x(“ccc”).ToString).First,
grp.Select(Function(x) x(“ddd”).ToString).First,
grp.Sum(Function (r) Convert.ToDouble(r.Item(“Amount”).ToString)),
grp.Select(Function(x) x(“fff”).ToString).First
}, True)).CopyToDataTable
after this you can merge the datatable containing PA4.

3 Likes
8

iam not able to understand the below code can u make is simple.

9

Hi @T_Y_Raju,

the first 3 lines of the code finds the unique values in the column “aaa” and then the next load datarow we need to add all the values that we need so for each of the unique values in “aaa”, grp.Select(Function(x) x(“bbb”).ToString).First finds the first value of in the datatable for that rows and grp.Sum(Function (r) Convert.ToDouble(r.Item(“Amount”).ToString)) sums the amount where all the rows contain the same value of the column “aaa”.

for example, the first 3 lines will give the group value as 1,3,10 and 6 and then for each of these values, for the column “bbb”,“ccc”,“ddd”,“fff” that it gets will be taken so for when “aaa” = 1, “bbb” = 1. When “aaa” = 3,“bbb”=2 and for amount it will sum all the rows for each value of the unique “aaa”.

1 Like