Click here to Skip to main content
14,266,769 members
Rate this:
Please Sign up or sign in to vote.
See more:
I need to do column summing of datatable. Datatable example as given bellow -
Datatable (dtrec)

 COL1   COL2  COL3   ID    AMT   PERCENTAGE  COL4
-----------------------------------------------------
 2022                1234  400   .20         213 
 2022                1234  400   .20


By using bellow linq statement i am receiving an output like bellow -
Datatable (dt)

 COL1   COL2  COL3   ID    AMT   PERCENTAGE  COL4
-----------------------------------------------------
                     1234  800     .40         


DataTable dt = dtrec.AsEnumerable()
                .GroupBy(r => r.Field<decimal>("ID"))
                 .Select(g =>
                 {
                       var row = dtrec.NewRow();
                       row["ID"] = g.Key;
                       row["AMT"] = g.Sum(r => r.Field<decimal>("AMT"));
                       row["PERCENTAGE"] = g.Sum(r => r.Field<decimal>("PERCENTAGE"));
                       return row;
                 }).CopyToDataTable();

Expected Result -

 COL1   COL2  COL3   ID    AMT   PERCENTAGE  COL4
-----------------------------------------------------
 2022                1234  800   .40         213 


Here data of COL1 and COL4 are getting blank, my requirement is that, a linq statement, that would select all columns from datatable and additionally it would be summing up AMT and PERCENTAGE columns. As shown in expected result.

Another important point to consider that apart from ID, AMT and PERCENTAGE columns other columns are dynamic i.e. columns might increase (e.g. COL1, COL2, COL3, COL4, COl5.... etc) hence we cannot include COL1 or COL4 inside select statement.

Any suggestion would be helpful.

Thanks

What I have tried:

DataTable dt = dtrec.AsEnumerable()
                .GroupBy(r => r.Field<decimal>("ID"))
                 .Select(g =>
                 {
                       var row = dtrec.NewRow();
                       row["ID"] = g.Key;<pre lang="text">
                       row["AMT"] = g.Sum(r => r.Field<decimal>("AMT"));
                       row["PERCENTAGE"] = g.Sum(r => r.Field<decimal>("PERCENTAGE"));
                       return row;
                 }).CopyToDataTable();
Posted
Updated 14-Jun-19 10:37am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
hence we cannot include COL1 or COL4 inside select statement.


Are you sure? ;)

Try this:
DataRow[] totals = dtrec.AsEnumerable()
	.GroupBy(x=>x["ID"])
	.Select(grp=> dt.LoadDataRow(new object[]
		{
			grp.Select(y=>y["COL1"]).FirstOrDefault(),
			grp.Select(y=>y["COL2"]).FirstOrDefault(),
			grp.Select(y=>y["COL3"]).FirstOrDefault(),
			grp.Key,
			grp.Sum(y=>y.Field<int>("AMT")),
			grp.Sum(y=>y.Field<double>("PERCENTAGE")),
			grp.Sum(y=>y.Field<int?>("COL4")==(int?)null ? 0 : y.Field<int?>("COL4"))
		}, false)
	).ToArray();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100