Remove rows with same column value from DataTable and add corresponding values

Question

I have a DataTable with multiple columns. If the value of certain column repeats, I need to remove that row and add the quantities against it. For example, following datatable

ITEM    QTY
------------
1       20
2       10
2       10
3       20

would become:

ITEM    QTY
-----------
1       20
2       20
3       20

This is what I did

var table = dt.AsEnumerable() 
.GroupBy(row => row.Field("ITEM")) 
.Select(group => group.First()) 
.CopyToDataTable();

It removes the extra row but doesn't add up the quantities. So please help me in this regard.

try this [enter link description here][1] [1]: http://stackoverflow.com/questions/4415519/best-way-to-remove-duplicate-entries-from-a-data-table — Learner, Aug 27 '13 at 10:40

score 7 · Accepted Answer · edited May 23 '17 at 11:57

You can use Sum. You just have to find the duplicate-rows first:

var dupGroups = dt.AsEnumerable()
    .GroupBy(row => row.Field<int>("ITEM"))
    .Where(g => g.Count() > 1);

Now you can use them to get the sum and to remove the redundant rows from the table.

foreach (var group in dupGroups)
{
    DataRow first = group.First();
    int sum = group.Sum(r => r.Field<int>("QTY"));
    first.SetField("QTY", sum);
    foreach (DataRow row in group.Skip(1))
        dt.Rows.Remove(row);
}

Or in one query which creates a new DataTable.

DataTable newTable = dt.AsEnumerable()
    .GroupBy(row => row.Field<int>("ITEM"))
    .Select(g => 
    {
        DataRow first = g.First();
        if (g.Count() > 1)
        {
            int sum = g.Sum(r => r.Field<int>("QTY"));
            first.SetField("QTY", sum);
        }
        return first;
    })
    .CopyToDataTable();

However, even the second approach modifies the original table which might be undesired since you use CopyToDatatable to create a new DataTable. You need to clone the original table(DataTable newTable = dt.Clone();) to get an empty table with the same schema. Then use NewRow + ItemArray.Clone() or table.ImportRow to create a real clone without modifying the original data.

See: C# simple way to copy or clone a DataRow?

Edit: Here is an example how you can create a clone without touching the original table:

DataTable newTable = dt.Clone();
var itemGroups = dt.AsEnumerable()
    .GroupBy(row => row.Field<int>("ITEM"));
foreach (var group in itemGroups)
{
    DataRow first = group.First();
    if (group.Count() == 1)
        newTable.ImportRow(first);
    else
    {
        DataRow clone = newTable.Rows.Add((object[])first.ItemArray.Clone());
        int qtySum = group.Sum(r => r.Field<int>("QTY"));
        clone.SetField("QTY", qtySum);
    }
}

Thanks for your answer and comments. The second approach is helpful. — Sara, Aug 27 '13 at 11:56
@Sara: But the second approach is the most "dangerous" since it modifies both tables in a subtle way. I would use the first if i don't want to create a new table and `table.Clone()` + `ImportRow` if i want to create a copy table with summed values. — Tim Schmelter, Aug 27 '13 at 12:10
@Sara: Added an example(untested) to show how you can create a clone with totalized data without modifying the original table. — Tim Schmelter, Aug 27 '13 at 15:40
Thank you for the effort. I used your second approach and tested it. It seems to me that original table remains the same. At least the number of rows remains the same. I didn't understand the subtle way in which original table gets modified. — Sara, Aug 28 '13 at 05:58
@Sara: The "subtle way in which original table gets modified" on my second approach (or Cuong Le's) is at `first.SetField("QTY", sum);`. This updates an existing `DataRow` instance of the original table, so the data gets changed. So you are ending with the same amount of rows but with totalized data on one `DataRow`. The `CopyToDataTable` at the end creates a new `DataTable` and copies all rows into it. However, the old table is now "corrupted". My first approach is more clear since it modifies only the original table and don't create a new one. Btw, consider to accept an answer :) — Tim Schmelter, Aug 28 '13 at 07:07

score 3 · Answer 2 · answered Aug 27 '13 at 10:40

3

var table = dt.AsEnumerable() 
    .GroupBy(row => row.Field<int>("ITEM")) 
    .Select(group => {
        var row = group.First();
        row['QTY'] = group.Sum(x => x.Field<int>('QTY'));
        return row;
    }).CopyToDataTable();

answered Aug 27 '13 at 10:40

cuongle

74,024
28
151
206

However, this modifies the original table which might be undesired since OP uses `CopyToDatatable` to create a new `DataTable`. Btw, the same problem has [my second approach](http://stackoverflow.com/a/18463169/284240). You need to clone the original table and use `NewRow` + `ItemArray.Clone()` or `table.ImportRow` to create a real clone without modifying the original data. – Tim Schmelter Aug 27 '13 at 10:55
@Cuong Le Thanks. It works. It modifies the original but that is the minor issue. – Sara Aug 27 '13 at 11:56

King King · Answer 3 · 2013-08-27T12:54:29.930

1

This won't change your original DataTable:

var table = dt.Copy().AsEnumerable()
                      .GroupBy(row=>row["ITEM"])
                      .Select(g=> {
                                DataRow dr = g.First();
                                dr.SetField("QTY", g.Sum(x=>x.Field<int>("QTY")));
                                return dr;
                             })
                      .CopyToDataTable();

edited Aug 27 '13 at 12:54

answered Aug 27 '13 at 10:38

King King

61,710
16
105
130

You also don't use `dr` anywhere – musefan Aug 27 '13 at 10:44
@musefan thanks, I don't know why I typed it wrongly. That would be `dr`. – King King Aug 27 '13 at 10:45

Remove rows with same column value from DataTable and add corresponding values

3 Answers3