Datatable group by with linq in vb.net

Question

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

Solution

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.

Comments

[Nick]

Thank you. The snippet was causing casting exception. But once I changed from "Of Int32" to "Of Decimal" all worked as charm.

[majjam]

Thank-you for this, I've been wrestling with a similar problem for ages