locked
Column Summing using Linq RRS feed

  • Question

  • User623533236 posted

    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 is getting blank, so need get values of these columns along with ID, AMT and PERCENTAGE.

    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). That's why cannot include COL1 or COL4 inside select statement.

    Hence require a linq statement in order to retrieve expected output i.e. selecting all columns of datatable and additionally it would be summing up AMT and PERCENTAGE columns.

    Thanks

    Tuesday, June 11, 2019 3:41 PM

All replies

  • User1520731567 posted

    Hi aditya.mukherjee.kol,

    Datatable (dtrec)
    
     COL1   COL2  COL3   ID    AMT   PERCENTAGE  COL4
    -----------------------------------------------------
     2022   2024   2021        1234  400   .20     213 
     2022   2025   2026        1234  400   .20

    Do column["COL*"] has different value?

    Best Regards.

    Yuki Tao

    Wednesday, June 12, 2019 10:13 AM
  • User623533236 posted

    Thanks for your reply, the columns values should be same apart from Amount and Percent column might have different values that we need make sum of on basis of IDs Now for the below datatable desired output is given bellow. So it can be said merging two row where ID's are same with same values in each columns and summing up its Amount and Percentage columns.

    Datatable (dtrec)
    
     COL1   COL2  COL3     ID     AMT   PERCENTAGE  COL4
    -----------------------------------------------------
     2022   2024   2021   5001    400     .20     213
     2022   2024   2021   5001    400     .20
    2031 2021 2134 6001 500 .20

    Desired Output - Datatable (dtrec)
    COL1 COL2 COL3 ID AMT PERCENTAGE COL4
    -----------------------------------------------------
    2022 2024 2021 5001 800 .40 213
    2031 2021 2134 6001 500 .20
    Wednesday, June 12, 2019 10:03 PM