Sum DataTable duplicate rows with group by using C# and VB.Net in Linq

RitwikSaha
 
on Jan 10, 2021 10:53 PM
Sample_355242.zip
3184 Views

Hi,

I have a Datatable like this:

Id             Amount 1        Amount 2        Amount 3  
1              2               2               2  
12             4               6               4  
12             6               6               5  
22             7               2               1  
22             7               2               2

I need to get my datatable like this:

Id             Amount 1        Amount 2        Amount 3  
1              2               2               2  
12             10              12              9    
22             14              4               3

How to get this?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 11, 2021 05:16 AM
on Jan 11, 2021 05:19 AM

Hi RitwikSaha,

Refer below code.

C#

DataTable dtOriginal = new DataTable();
dtOriginal.Columns.AddRange(new DataColumn[]
{
    new DataColumn("Id"),
    new DataColumn("Amount 1"),
    new DataColumn("Amount 2"),
    new DataColumn("Amount 3")
});
dtOriginal.Rows.Add(1, 2, 2, 2);
dtOriginal.Rows.Add(12, 4, 6, 4);
dtOriginal.Rows.Add(12, 6, 6, 5);
dtOriginal.Rows.Add(22, 7, 2, 1);
dtOriginal.Rows.Add(22, 7, 2, 2);

DataTable dtFinal = dtOriginal.AsEnumerable()
.GroupBy(r => r["Id"])
.Select(x =>
{
    var row = dtOriginal.NewRow();
    row["Id"] = x.Key;
    row["Amount 1"] = x.Sum(r => Convert.ToInt32(r["Amount 1"]));
    row["Amount 2"] = x.Sum(r => Convert.ToInt32(r["Amount 2"]));
    row["Amount 3"] = x.Sum(r => Convert.ToInt32(r["Amount 3"]));

    return row;
}).CopyToDataTable();

VB.Net

Dim dtOriginal As DataTable = New DataTable()
dtOriginal.Columns.AddRange(New DataColumn() {
                            New DataColumn("Id"),
                            New DataColumn("Amount 1"),
                            New DataColumn("Amount 2"),
                            New DataColumn("Amount 3")})
dtOriginal.Rows.Add(1, 2, 2, 2)
dtOriginal.Rows.Add(12, 4, 6, 4)
dtOriginal.Rows.Add(12, 6, 6, 5)
dtOriginal.Rows.Add(22, 7, 2, 1)
dtOriginal.Rows.Add(22, 7, 2, 2)
Dim dtFinal As DataTable = dtOriginal.AsEnumerable() _
.GroupBy(Function(r) r("Id")) _
.Select(Function(x)
            Dim row = dtOriginal.NewRow()
            row("Id") = x.Key
            row("Amount 1") = x.Sum(Function(r) Convert.ToInt32(r("Amount 1")))
            row("Amount 2") = x.Sum(Function(r) Convert.ToInt32(r("Amount 2")))
            row("Amount 3") = x.Sum(Function(r) Convert.ToInt32(r("Amount 3")))
            Return row
        End Function).CopyToDataTable()