Grouping data with Count

[craig.norton]

I have a datatable with the following fields

Branch, Item, Qty
Brisbane, Paper, 15
Brisbane, Pens,45
Brisbane, Paper, 20
Brisbane, Marker, 50
Brisbane, Pens, 10
Brisbane, Paper 80

I want to build a query that groups by Branch and Item and also does a count of the Item. The result would look like this.

Brisbane, Paper, 3
Brisbane, Pens, 2
Brisbane, Marker, 1

After running the query I want to add the results to a Datatable of the same format.

What is the most efficient way to do this. Using LINQ?

[Anil_G (Anil Gorthi)]

@craig.norton

linq would fit…using build datatble create a table with 3 columns two string and one integer

assign with

newdt = dt.AsEnumerable.GroupBy(function(x) x("Branch").ToString+x("Item").ToString).Select(function(x) newdt.LoadDataRow({x.First()("Branch").ToString,x.First()("Item").ToString,x.Count},False)).CopyToDataTable

cheers

[Vinit_Mhatre (BotBuilder)]

Hi @craig.norton ,

You can use LINQ for this,

InputDt:

OutputDT:

Flow:

Linq query:

(From row In Input_DT.AsEnumerable()
          Group row By branch = row("Branch").ToString.Trim, item = row("Item").ToString.Trim Into Group
          Select Output_DT.Rows.Add(branch, item, Group.Count())).CopyToDataTable()

Output:

Regards,
Vinit Mhatre

[zoyebmansuri]

Please use below LINQ to group your DataTable by Branch and Item and count the occurrences of each Item

var grouped = dt.AsEnumerable()
.GroupBy(row => new {
Branch = row.Field(“Branch”),
Item = row.Field(“Item”)
})
.Select(g => new {
Branch = g.Key.Branch,
Item = g.Key.Item,
Count = g.Count()
});

[craig.norton]

@Anil_G

newdt = dt.AsEnumerable.GroupBy(function(x) x(“Branch”).ToString+x(“Item”).ToString).Select(function(x) newdt.LoadDataRow({x.First()(“Branch”).ToString,x.First()(“Item”).ToString,x.Count},False)).CopyToDataTable

@Anil_G This worked perfectly. Many thanks.