I'm trying to get aggregate values from data table. But can't figure out how. Saw some examples in c#, but couldn't translate those to vb.net.
I have data table
Month, campaign, sales, leads, gross
1 1 5 10 1000
1 2 0 5 0
2 1 2 0 300
2 2 1 3 200
I need to get a result :
Month, sales, leads, gross
1 5 15 1000
2 3 3 500
I don't want to loop and combine values manually. Please help
You want to Group by Month
? You can use Sum
to sum the groups:
Dim query = From row In dt
Group row By Month = row.Field(Of Int32)("Month") Into MonthGroup = Group
Select New With {
Key Month,
.Sales = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Sales")),
.Leads = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Leads")),
.Gross = MonthGroup.Sum(Function(r) r.Field(Of Int32)("Gross"))
}
For Each x In query
Console.WriteLine("Month:{0} {1} {2} {3}", x.Month, x.Sales, x.Leads, x.Gross)
Next
This is a mixture of Linq query- and method-syntax.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With