Sum the amount if id is duplicate

Help Studio
, ,
1

I have one data table/ excel in which there are lots of columns, first column is id and one of the columns is Amount, and that amount is filled with values. I want to sum up the amount of the rows whose ID are duplicate , and in result data table all non duplicate rows also should be there

[id ] ---- [sum] ---- [rest of coulums with ffilled values…
1 ---- 10----
2 ---- 20----
3 ---- 30----
4 ---- 40—
1 -----20—
1 – – 20—
2 ---- 10—

result

id ---- sum ---- rest of coulmns with filled values
1 ---- 50
2 ---- 30
3 ---- 30
4 ---- 40

I want a linq query

2

we wold recommend to recheck the above requirement part

dictLK | Dictionary (Of String, Int32) =

(From d in YourDataTable.AsEnumerable
Group d by k=d("id").toString.Trim into grp=Group
Let cn = grp.Sum(Function (g) CInt(g("sum").toString.Trim))
Select t = Tuple.Create(k,cn)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)
3

@adishjain

Please try this

Use build datatable and create a datatbe with two columns if type string

Newdt = dt.AsEnumerable.GroupBy(function(x) x("id").ToString).Select(function(x) New {x.key,x.Sum(function(y) cdbl(y("sum").ToString).ToString}).CopyToDataTable

Cheers

4

yes PPR non duplicate rows should also be there along with duplicate(single)

5

it is showing Type name expected error and type or with expected

6

@adishjain

Pleaze try adding with after new keyword new with {…

Cheers

7

when grouping we do sum up all group members. But you are listing the need of additiona columns

here is not specified a rule for fetching the values e.g. first group member, last …

8

I didnot understand @ppr

9

tried but not working

10

did you request an output datatable with more than 2 cols?

11

Hi @adishjain ,

Step 1: Assign dtResults = dt.clone

Step 2: Assign dtResults = (From row In Dt.AsEnumerable
Group row By col1=row(0).ToString.Trim Into grp = Group
Select dtResults.Rows.Add({col1, grp.Sum(Function (x) CInt(x(1).toString.Trim))})).CopyToDataTable

This will work even unique value exists in the datatable.

If this works. Please provide as solution

Thanks,
Darshan

12

hello darshan, my column index in not fix, can you please make it by ID and SUM name so that if my index changes the it can process data by column names

13

Sure @adishjain

Step 1: Assign dtResults = dt.clone

Step 2: Assign dtResults = (From row In Dt.AsEnumerable
Group row By col1=row(“ID”).ToString.Trim Into grp = Group
Select dtResults.Rows.Add({col1, grp.Sum(Function (x) CInt(x(“Sum”).toString.Trim))})).CopyToDataTable

That’s it.

Thanks

14

@adishjain

Have corrected it

dt.AsEnumerable.GroupBy(function(x) x(“id”).ToString).Select(function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(r) CDBL(r(“B”).ToString)).ToString},False)).CopyToDataTable

or use as following

( From row In dt.AsEnumerable
	Group row By k=row("id").ToString.Trim
	Into grp=Group
	Let cs = grp.Sum(Function(x) CDbl(x("Sum").ToString))
	Select dt2.Rows.Add({k, cs.ToString})
).CopyToDataTable

cheers

15

it is giving result but rest of all data is getting removed from data table means only two columns have data and rest of columns come empty

16

@adishjain

I have given it as per the structure you have show …if there are more column add those details in {k, cs.ToString} like grp.First()("ColumnName").ToString in the same order you need

cheers

17

we adressed this for requirement clearing by:

And

18

Hi @adishjain

Try this-

var query = from row in originalDataTable.AsEnumerable()
group row by row.Field(“id”) into grp
select new
{
id = grp.Key,
sum = grp.Sum(r => r.Field(“Amount”))
};

DataTable resultDataTable = new DataTable();
resultDataTable.Columns.Add(“id”, typeof(int));
resultDataTable.Columns.Add(“sum”, typeof(int));

foreach (var item in query)
{
resultDataTable.Rows.Add(item.id, item.sum);
}

var nonDuplicateRows = originalDataTable.AsEnumerable()
.GroupBy(row => row.Field(“id”))
.Where(grp => grp.Count() == 1)
.Select(grp => grp.First());

foreach (var row in nonDuplicateRows)
{
DataRow newRow = resultDataTable.Rows.Add();
foreach (DataColumn column in originalDataTable.Columns)
{
newRow[column.ColumnName] = row[column.ColumnName];
}
}

Thanks!!