CODEWITHSUNDEEP
vb.netdatatable
Grey Walker
Grey Walker

Reputation: 125

Increment column value in datatable if duplicate row is found

I am trying to summarize some data in a data table, what I am attempting to do is to sum the total quantity of duplicate rows found.

My data table looks like this.

|ForeName|SurName|Quantity|
|Dave    |Smith  | 10000  |
|Dave    |Smith  | 20000  |
|Dave    |Smith  | 30000  |
|John    |Peacock| 10000  |

I want to summarize this data to look like this.

|ForeName|SurName|Quantity|
|Dave    |Smith  | 60000  |
|John    |Peacock| 10000  |

At the moment I am searching for duplicates in the data table 

Dim duplicates = From rows In dt.AsEnumerable().GroupBy(Function(r) New With {Key .a = r("ForeName"), Key .b = r("SurName")}).Where(Function(gr) gr.Count() > 1).ToList()

However where to proceed from here I am unsure, has anyone ever came across a scenario like this and able to point me in the right direction.

Upvotes: 0

Views: 394

Answers (2)

Mark
Mark

Reputation: 8160

If you don't mind that the output is anonymous types instead of DataRows, you could use a LINQ query like this:

Dim summary = 
    From r In dt.AsEnumerable()
    Group By ForeName = r.Field(Of String)("ForeName"), SurName = r.Field(Of String)("SurName") Into Group
    Select ForeName, SurName, Quantity = Group.Sum(Function(x) x.Field(Of Integer)("Quantity"))

ForeName | SurName | Quantity
---------+---------+---------
Dave     | Smith   |    60000
John     | Peacock |    10000

You could use the answers to this question to convert it back into a DataTable, if needed.

Adding the count, like in Plutonix's answer would simply mean adding Group.Count to the Select clause:

Dim summary = 
    From r In dt.AsEnumerable()
    Group By ForeName = r.Field(Of String)("ForeName"), SurName = r.Field(Of String)("SurName") Into Group
    Select ForeName, SurName, Group.Count, Quantity = Group.Sum(Function(x) x.Field(Of Integer)("Quantity"))

ForeName | SurName | Count | Quantity
---------+---------+-------+---------
Dave     | Smith   |     3 |    60000
John     | Peacock |     1 |    10000

Upvotes: 1

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38875

Group the rows by name(s) or ID; use the name/ID as the identifier in a new anon type, and a Count/Total or Quantity property to collect the Sum of the Quantity:

Dim duplicates = myDT.AsEnumerable().
                 GroupBy(Function(r) New With {Key .Item = r.Field(Of String)("Name")}).
                 Select(Function(g) New With {Key .Name = g.Key.Item,
                                              Key .Count = g.Count,
                           Key .Total = g.Sum(Function(s) s.Field(Of Int32)("Quantity"))}).
                                      OrderByDescending(Function(j) j.Total).
                                      ToList()

For "fun" it also counts the number of source/group rows and orders them by the total.

enter image description here

Upvotes: 1

Related Questions